SQL Server 存储过程与触发器

一、触发器

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 没有记录集的概念