tsmsbs项目中用到的触发器和存储过程

-- 触发器
create   trigger  tg_cranedata  on  dbo.CraneData
for   insert
as
begin
   declare   @id   int , @craneid   int , @acttime   datetime , @height   int , @range   int , @weight   int , @moment   real , @hoistspeed   real , @rangespeed   real
   declare   @xacttime   datetime , @xheight   int , @xrange   int
   declare   @xtime   int
   select   @id = id, @craneid = craneid, @acttime = acttime, @height = height, @range = range, @weight = weight  from  inserted
   select   @xacttime = acttime, @xheight = height, @xrange = range  from  cranedata  where  id = ( select   top   1  id  from  cranedata  where  craneid = @craneid   and  id < @id   order   by  id  desc )
   set   @moment = @range * @weight * 9.8 / 100000
   set   @xtime = datediff (s, @xacttime , @acttime )
   if   @xtime < 10   and   @xtime > 0
   begin  
     set   @hoistspeed = ( @height - @xheight ) / @xtime
     set   @rangespeed = ( @range - @xrange ) / @xtime
     update  cranedata  set  moment = @moment ,hoistspeed = @hoistspeed ,rangespeed = @rangespeed   where  id = @id
   end
   else
   begin
     update  cranedata  set  moment = @moment ,hoistspeed = 0 ,rangespeed = 0   where  id = @id
   end
end


-- 存储过程
create   proc  CutCraneData
as

drop   trigger  tg_cranedata

declare   @postfix   varchar ( 20 )
declare   @newname   varchar ( 20 )
select   @postfix = convert ( varchar , getdate (), 112 )
select   @newname = ' CraneData ' + @postfix

EXEC  SP_RENAME  ' CraneData ' , @newname

CREATE   TABLE   [ dbo ] . [ CraneData ]  (
     [ Id ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ CraneID ]   [ int ]   NULL  ,
     [ ActTime ]   [ datetime ]   NULL  ,
     [ ActMillisecond ]   [ int ]   NULL  ,
     [ Height ]   [ real ]   NULL  ,
     [ Range ]   [ real ]   NULL  ,
     [ SlewRange ]   [ int ]   NULL  ,
     [ Weight ]   [ int ]   NULL  ,
     [ Moment ]   [ real ]   NULL  ,
     [ HoistSpeed ]   [ real ]   NULL  ,
     [ RangeSpeed ]   [ real ]   NULL  ,
     [ IsOverRun ]   [ tinyint ]   NULL  
)  ON   [ PRIMARY ]

declare   @sqlCreateTrigger   nvarchar ( 1500 )
set   @sqlCreateTrigger = N ' create  trigger tg_cranedata on dbo.CraneData
for insert
as
begin
  declare @id int,@craneid int,@acttime datetime,@height int,@range int,@weight int,@moment real,@hoistspeed real,@rangespeed real
  declare @xacttime datetime,@xheight int,@xrange int
  declare @xtime int
  select @id=id,@craneid=craneid,@acttime=acttime,@height=height,@range=range,@weight=weight from inserted
  select @xacttime=acttime,@xheight=height,@xrange=range from cranedata where id=(select top 1 id from cranedata where craneid=@craneid and id<@id order by id desc)
  set @moment=@range*@weight*9.8/100000
  set @xtime=datediff(s,@xacttime,@acttime)
  if @xtime<10 and @xtime>0
  begin 
    set @hoistspeed=(@height-@xheight)/@xtime
    set @rangespeed=(@range-@xrange)/@xtime
    update cranedata set moment=@moment,hoistspeed=@hoistspeed,rangespeed=@rangespeed where id=@id
  end
  else
  begin
    update cranedata set moment=@moment,hoistspeed=0,rangespeed=0 where id=@id
  end
end '

EXEC ( @sqlCreateTrigger )