mssql sqlserver sql脚本实现触发器中多个数据表之间联动(增加、修改、删除)操作操作的方法分享


摘要:
下文通过举例的方式讲述触发器中的insert update delete联动操作,如下所示:
实验环境:sql server 2008 R2

例:
下文通过举例的方式讲述“maomao365.comA”表上的进行增删改时,通过触发器自动联动操作至“maomao365.comB”表上的实现方式。

 
    ---建立基础表"maomao365.comA"和"maomao365.comB"
create table  [maomao365.comA]
(keyIdA int identity,
 infoA  varchar(100))
 go
 create table [maomao365.comB]
 (keyIdB int identity,keyIdAS int,
  infoB varchar(100))
  go

  ---创建增、删、改联动触发器
  ---同步新增触发器
  create trigger tr_maomao365_inserted
    on [maomao365.comA]
	 after insert 
  as
  begin
       insert into [maomao365.comB]
	   (keyIdAS,infoB)
	   select keyIdA,infoA from inserted 
  end
  go

  ---同步删除触发器
  create trigger tr_maomao365_delete
    on [maomao365.comA]
	 after delete 
  as
  begin
    delete from  [maomao365.comB]
	where keyIdAS in (select keyIdA from deleted) 
  end
  go


---同步修改触发器
  create trigger tr_maomao365_update
    on [maomao365.comA]
	 after update  
  as
  begin
    update   B 
	set B.infoB = a.infoA
	 from   [maomao365.comB]  B ,inserted a 
	 where  b.keyIdAS =a.keyIdA 
	 end
  go

  

  insert into [maomao365.comA]
  (infoA)values('sqlserver'),
  ('maomao')
  go

  select * from [maomao365.comB]


  update [maomao365.comA] 
  set infoA ='test' where keyIdA =1 


  select * from [maomao365.comB]


  delete [maomao365.comA] 
  where keyIdA =1 
  
  
  select * from [maomao365.comB]
  



  go

  drop table [maomao365.comA]
  drop     table [maomao365.comB]
  
mssql_sqlserver_触发器实现的数据联动

mssql_sqlserver_触发器实现的数据联动