使用遊標的觸發器

-- =============================================
-- Author:  吳會然
-- Create date: 2008-05-22
-- Description: 刪除點播節目的觸發器
--    在polling中插入一條記錄
-- =============================================
CREATE TRIGGER [dbo].[tr_vodprogramdelete]
   ON  [dbo].[tbl_vodprogram]
   AFTER DELETE
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for trigger here
 declare @programid int
 select programid from deleted
 if( @@rowcount > 1)
 begin
  declare step2 cursor for
  select programid from deleted
  open step2
  fetch next from step2 into @programid
  while @@fetch_status=0
  begin
   insert into tbl_polling (type,effecteid,flag,effecttable) VALUES (5,@programid,3,'tbl_vodprogram')
   update tbl_liveprogram set lastmodifytime=dbo.GetTime_T() where programid=@programid
   fetch next from step2 into @programid
  end
  close step2
  deallocate step2
 end
 else
 begin
  select @programid=programid from deleted
  insert into tbl_polling (type,effecteid,flag,effecttable) VALUES (5,@programid,3,'tbl_vodprogram')
 end
END