sqlserver trigger

標籤：

 1 --==================================== 2 --  Create database trigger template  3 --==================================== 4 USE [EasyJobExTest] 5 GO 6  7 --判斷觸發器是否存在,存在則刪除 8 IF EXISTS( 9   select top 1 t.name as trigger_name,a.name as table_name from sys.triggers t,sys.objects a 10 where Lower(a.name)=‘section‘ and t.parent_id=a.object_id and Lower(t.name)=‘tr_section_insert‘11 )12 begin13     DROP TRIGGER tr_section_insert;14 end15 GO16 17 /*before觸發器instead of*/18 CREATE TRIGGER tr_section_insert ON section 19     instead of insert,delete,update20     --for delete --after觸發器delete21     --for insert --after觸發器insert22     --for update --after觸發器update23 AS 24 begin25 declare @SecId    int;26 declare @SecName    nvarchar(50);27 declare @Remark    nvarchar(200);28 declare @Department    int;29 declare @CreateTime    datetime;30 declare @Deepness    int;31 declare @ManageUserID    int;32 33 select @SecId=secid,@SecName=t.SecName,@Remark=t.Remark,@Department=t.Department,@CreateTime=t.CreateTime,34 @Deepness=t.Deepness,@ManageUserID=t.ManageUserID from inserted t;35 36 if exists(select top 1 * from inserted)37 begin38 print ‘trigger insert‘39     IF @CreateTime is NULL40     begin41         select @CreateTime = Sysdatetime()42         print ‘目前時間：‘43         print @CreateTime44     end45         insert into Section values(@SecId,@SecName,@Remark,@Department,@CreateTime,@Deepness,@ManageUserID);46 end47 else if exists(select top 1 * from deleted)48 begin49 print ‘trigger delete‘50 end51 else52 print ‘trigger update‘53 54 /*55 IF IS_MEMBER (‘db_owner‘) = 056 BEGIN57    PRINT ‘You must ask your DBA to drop or alter tables!‘ 58    ROLLBACK TRANSACTION59 END60 */61 end62 go

 

sqlserver trigger