SQL server 触发器的使用


 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