SQL server触发器修改表另一表 sql触发器修改数据

SQL Server中触发器常用于对一个表的更新、插入及删除后操作，和Oracle触发器类似，都有after触发器类型，但SQL Server没有before类型。

原理不多讲,上一张网上的原理图,差不多就很清楚了.

下面通过一个例子实现在SQL Server 中通过触发器删除、插入和修改数据并同步到新表

1 ---创建一张表
 2 create table test_01
 3 (
 4    id int identity(1,1)  primary key,
 5    userID  int not null,
 6    name varchar(50)  not null,
 7    sex nvarchar(2)  not null,
 8    age int null
 9 )
10 
11 ---复制表结构到新表
12 select * into test_02 from test_01

然后，我们创建一个触发器

1 --已存在，先删除
 2 if(exists (select * from  sysobjects where xtype='tr' and name='tri_test01'))
 3 begin
 4   drop trigger tri_test01
 5   print '已删除'
 6   end
 7   go
 8 
 9   ---创建触发器
10 create trigger tri_test01 on test_01
11 after insert,update,delete
12 as
13 begin
14     --禁止返回受影响行数
15     --SET NOCOUNT ON;
16 
17     declare  @isDel  bit  =  0
18     declare  @isIns  bit  =  0
19     declare  @changeType varchar(20)
20 
21     -- 如果在DELETED内部临时触发表找到记录，说明旧数据被删除 
22     if  exists( select  top  1  1  from deleted)
23        set  @isDel  =  1
24     
25     -- 如果在inserted内部临时触发表找到记录，说明有新数据插入     
26     if  exists( select  top  1  1  from inserted)
27          set  @isIns  =  1  
28 
29     -- 如果两个表都有记录，说明触发器是执行更新触发
30     if  @isIns  =  1  and  @isDel  =  1 
31         set @changeType='UPDATE'
32 
33     -- 如果变量@i值被变更为1，而变量@d没有变更，说明触发器是执行插入触发    
34     if  @isIns  =  1  and  @isDel  =  0
35         set @changeType='INSERT'
36  
37     -- 下面判断成立，说明说明触发器是执行删除触发    
38     if  @isIns  =  0  and  @isDel  =  1
39         set @changeType='DELETE'
40 
41     declare @userID int,@age int,@sex nvarchar,@maxid int
42     if(@changeType='INSERT')
43            begin
44          print(' 插入 ')
45          select @userID=userID,@age=age,@sex=sex from inserted
46 
47          ---如果触发器类型是before，此处可以判断ID是否存在重复
48          ---可惜SQL Server不支持before触发器
49         --if exists ( select 1 from test_01  where userID = @userID )
50         --  begin
51         --      print '用户ID不能重复 '
52         --      rollback transaction   --回滚
53         --  end
54         
55         if(@age>=200 AND @age is not null)
56           begin
57               print '人不可能长命百岁 '
58               rollback transaction   --回滚
59           end
60         else if(@sex!='男' AND @sex!='女')
61           begin
62               print '人不可能不男不女 '
63               rollback transaction   --回滚
64           end
65          else
66            begin    
67              insert into test_02(userID,name,sex,age)
68              select userID,name,sex,age from inserted  --插入到新表
69            end
70         end
71         ----
72       else if(@changeType='UPDATE')
73       begin
74         print ('更新')
75         if UPDATE(age)  ---更新age字段
76         begin
77               update test_02 set age  = inserted.age
78               from test_02 ,deleted,inserted
79               where test_02.age = deleted.age
80         end
81       end
82       ----
83       else if(@changeType='DELETE')
84       begin
85          select  @maxid = userID  from  deleted; 
86         if exists ( select count(1) from  test_02  where userID = @maxid )
87         begin 
88            delete  from test_02  where  userID = @maxid;    
89         end
90       end
91 end

以上触发器中，如果要判断一个用户ID是否已经存在，假设我没有为这个ID加入主键，想通过触发器判断，那么alter触发器不能实现,可以在评论说说你的解决方法.

如果触发器为更新，那么需要先删除新表已有数据，再新插入数据。例子中给的age字段只是示例，实际操作中建议以主键字段来判断。

删除的情况下，只用判断主键是否存在，已存在同步删除就OK了。

看看实际效果。。。

由于插入的时候存在触发验证的情况,若验证不通过将回滚操作,但已生成的主键值不会跟着回滚,所以两张表的主键ID(此处为自动生成的主键),可能有所不同.