1 --1.查看建立于某个表的触发器
2 SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND parent_obj = OBJECT_ID('表名')
3
4 --2.查看数据库中所有的触发器
5 --use 数据库名
6 --go
7 --select * from sysobjects where xtype='TR'
8 --sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。
9
10
11 --3.sp_helptext 查看触发器内容
12 --use 数据库名
13 --go
14 --exec sp_helptext '触发器名称'
15
16 --4.删除触发器
17 --drop trigger 触发器名称
18
19 --5.触发器的使用
20 --5.1、添加
21 go
22 create trigger eqadd
23 on [RT.Face].[dbo].[Equipment]
24 after insert
25 as
26 begin
27 declare @DeviceNo varchar(50),@ProjectId int,@DeviceType int
28 select @DeviceNo=DeviceNo,@ProjectId=ProjectId,@DeviceType=DeviceType from inserted
29 if(@DeviceType=2)
30 begin
31 insert into [RT.Face].[dbo].[CameraConfiguration](ProjectId, DeviceNo, Retrograde, RedLight, AbnormalDriving, SpeedMonitoring,
32 OpportunityDominates, LicensePlateRestrictions, TurnAround, EmergencyLanesOccupancy, ITime, uptType)values(@ProjectId,@DeviceNo,1,1,1,1,1,1,1,1,getdate(),0);
33 end
34 end
35
36 --5.2 修改
37 go
38 create trigger equpt
39 on [RT.Face].[dbo].[Equipment]
40 after update
41 as
42 begin
43 declare @DeviceNo varchar(50),@ProjectId int,@DeviceType int,@DeviceNos varchar(50),@DeviceTypes int,@counts int
44 select @DeviceNo=DeviceNo,@ProjectId=ProjectId,@DeviceType=DeviceType from inserted
45 select @DeviceNos=DeviceNo,@DeviceTypes=DeviceType from deleted
46 if(@DeviceNo!=@DeviceNos and @DeviceTypes=2)
47 begin
48 update [RT.Face].[dbo].[CameraConfiguration] set DeviceNo=@DeviceNo,ProjectId=@ProjectId where DeviceNo=@DeviceNos;
49 end
50 select @counts=count(*) from [RT.Face].[dbo].[CameraConfiguration] where DeviceNo=@DeviceNo
51 if(@counts<1)
52 begin
53 if(@DeviceType=2)
54 begin
55 insert into [RT.Face].[dbo].[CameraConfiguration](ProjectId, DeviceNo, Retrograde, RedLight, AbnormalDriving, SpeedMonitoring,
56 OpportunityDominates, LicensePlateRestrictions, TurnAround, EmergencyLanesOccupancy, ITime, uptType)values(@ProjectId,@DeviceNo,1,1,1,1,1,1,1,1,getdate(),0);
57 end
58 else
59 begin
60 delete from [RT.Face].[dbo].[CameraConfiguration] where DeviceNo=@DeviceNo;
61 end
62 end
63
64 end
65
66
67 --5.3 删除
68 go
69 create trigger eqdel
70 on [RT.Face].[dbo].[Equipment]
71 after delete
72 as
73 begin
74 declare @DeviceNo varchar(50),@DeviceType int
75 select @DeviceNo=DeviceNo,@DeviceType=DeviceType from deleted
76 if(@DeviceType=2)
77 begin
78 delete from [RT.Face].[dbo].[CameraConfiguration] where [DeviceNo]=@DeviceNo
79 end
80 end
