使用触发器操作表1(添加，更新，删除) 同步实现表2的代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】使用触发器操作表1(添加，更新，删除) 同步实现表2的

作者:黎昕 / 发布于2016/4/26/ 389

if object_id('TABLE_1') is not null drop table TABLE_1
CREATE TABLE TABLE_1
(
    ID INT primary key,
    Name1 nchar(10),
    Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
    ID INT primary key,
    Name1 nchar(10),
    Name2 nchar(10)
)

INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')



create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
   if exists(select 1 from inserted) and not exists(select 1 from deleted)--insert触发器
  begin
    insert into Table_1 select * from inserted
  end
  else if exists(select 1 from inserted) and  exists(select 1 from deleted)--update触发器

  begin
   update b1 
   set b1.Name1=U.Name1,b1.Name2=U.Name2
   from Table_1 b1,deleted U 
   where b1.ID=U.ID
  end
  else
  begin
  delete Table_1 where ID=(select ID from deleted)
  end
end


--插入测试

INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1

ID          Name1      Name2
----------- ---------- ----------
1           adsd       Chi       
2           Alex       Chi       
3           huguo      Chi       

(3 行受影响)

--删除测试
delete from Table_2 where ID=3

ID          Name1      Name2
----------- ---------- ----------
1           adsd       Chi       
2           Alex       Chi       

(2 行受影响)
--更新测试

select * from Table_2

update Table_2 set Name1='ALex2' where ID=2

ID          Name1      Name2
----------- ---------- ----------
1           adsd       Chi       
2           ALex2      Chi       

(2 行受影响)

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

同语言下

可能有用的

黎昕贡献的其它代码(12)