create trigger tri_a on table1 for insert
as
if exists(select * from table2,inserted where inserted.a1=table2.a1 and inserted.a2=table2.a2)
begin
update table2 set内容
end
else
begin
insert into table2 select 内容 from inserted
end
go
as
if exists(select * from table2,inserted where inserted.a1=table2.a1 and inserted.a2=table2.a2)
begin
update table2 set内容
end
else
begin
insert into table2 select 内容 from inserted
end
go
as
if exists(select 1 from table2,inserted where inserted.a1=table2.a1 and inserted.a2=table2.a2)
begin
update table2
set table2.a1=inserted.a1,
table2.a2=inserted.a2,
table2.a3=inserted.a3 from inserted
end
else
begin
insert into table2 select * from inserted
end
go
set table2.a1=inserted.a1,
table2.a2=inserted.a2,
table2.a3=inserted.a3 from inserted
改成:
update table2
set table2.a3=inserted.a3 from inserted
where table2.a1=inserted.a1 and
table2.a2=inserted.a2,
as
if exists(select * from table2,inserted where inserted.a1=table2.a1 and inserted.a2=table2.a2)
begin
update table2 set a3=inserted.a3 from inserted,table2 where table2.a1=inserted.a1 and table2.a2=inserted.a2
end
else
begin
insert into table2 select 内容 from inserted
end
go
在执行insert,update,delete的时候,会产生两个逻辑表,一个是INSERTED和DELETED。其中INSERTED存放着添加的内容,DELETED存放着删除的内容。UPDATE特殊,它是先删除,然后在添加,所以在INSERTED和DELETED中都有内容。