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 begin
13 DROP TRIGGER tr_section_insert;
14 end
15 GO
16
17 /*before触发器instead of*/
18 CREATE TRIGGER tr_section_insert ON section
19 instead of insert,delete,update
20 --for delete --after触发器delete
21 --for insert --after触发器insert
22 --for update --after触发器update
23 AS
24 begin
25 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 begin
38 print 'trigger insert'
39 IF @CreateTime is NULL
40 begin
41 select @CreateTime = Sysdatetime()
42 print '当前时间：'
43 print @CreateTime
44 end
45 insert into Section values(@SecId,@SecName,@Remark,@Department,@CreateTime,@Deepness,@ManageUserID);
46 end
47 else if exists(select top 1 * from deleted)
48 begin
49 print 'trigger delete'
50 end
51 else
52 print 'trigger update'
53
54 /*
55 IF IS_MEMBER ('db_owner') = 0
56 BEGIN
57 PRINT 'You must ask your DBA to drop or alter tables!'
58 ROLLBACK TRANSACTION
59 END
60 */
61 end
62 go
