一、触发器
USE [index]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[trigger_compute_speed] on [dbo].[index_new]
After insert
as
DECLARE @linkid int
DECLARE @speed float
DECLARE @dt datetime
select @linkid= inserted.linkid,@dt =
inserted.dtime from inserted
insert into dbo.averspeed(linkid,dtime,avespeed) select inserted.linkid,Dateadd(mi,5,inserted.dtime), (select 1.0*sum(carnum*rlength)/sum(carnum*rlength/speed) from dbo.index_new where linkid = @linkid and dtime > @dt-1.0*15.1/(24*60)) from inserted
主要刚刚插入的值用inserted
二、存储过程
create proc AverateSpeedProc
@dt datetime
as
begin
DECLARE @dtime datetime
DECLARE @speed float
DECLARE @length float
DECLARE @linkid int
DECLARE @len float
DECLARE @classid int
DECLARE cursor_links CURSOR FOR select speed,length,dtime,linkid,len from dbo.AM_20100906 where dtime = @dt
OPEN cursor_links
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM cursor_links INTO @speed,@linkid,@dtime,@classid,@length,@len insert into dbo.index_new (speed,linkid,dtime,classid,rlength,carnum) values (@speed,@linkid,@dtime,@classid,@length,@len)
END
close cursor_links
deallocate cursor_links
end
exec AverateSpeedProc '2011-09-06 07:30:00'
注意 SqlServer 与 Oracle 的区别，SqlServer 没有记录集的概念