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_1 VALUES(2,'xiao','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')
if exists (select name
from sysobjects
where name = 'tr_info'
and type = 'tr')
drop trigger tr_info
go
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)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)
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
select * from TABLE_2
--删除测试
delete from Table_2 where ID=3
select * from TABLE_1
select * from TABLE_2
--更新测试
update Table_2 set Name1='ALex2' where ID=2
select * from TABLE_1
select * from TABLE_2
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_1 VALUES(2,'xiao','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')
if exists (select name
from sysobjects
where name = 'tr_info'
and type = 'tr')
drop trigger tr_info
go
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)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)
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
select * from TABLE_2
--删除测试
delete from Table_2 where ID=3
select * from TABLE_1
select * from TABLE_2
--更新测试
update Table_2 set Name1='ALex2' where ID=2
select * from TABLE_1
select * from TABLE_2