tsmsbs项目中用到的触发器和存储过程
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--触发器
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)
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)