create trigger triggername
on tb1
for insert
as
begin
insert into db2..tb1 select * from inseted
endgo
create trigger triggername
on tb1
for update
as
begin
update a
set a.col = i.col
from db2..tb1 a
left join inserted i
on i.id = a.id
end
on tb1
for insert
as
begin
insert into db2..tb1 select * from inseted
endgo
create trigger triggername
on tb1
for update
as
begin
update a
set a.col = i.col
from db2..tb1 a
left join inserted i
on i.id = a.id
end
实例(local)内有两个数据库DB1和DB2,
并且两数据库结构完全相同,业务需要在对DB1中的表tb1更新或者插入时候,
同步在DB2的同名表tb1中也update或者insert,如何做这种跨数据库的操作??use DB1
go
create trigger tb1_insert on tb1
after insert
as
insert DB2.dbo.tb1 select * from inserted --有自增列是要指定列名go
create trigger tb1_update on tb1
after update
as
update T
set col1=i.col1,col2=i.col2...指定要更新列
from
db2.dbo.tb1 t
join
inserted i on t.唯一值=i.唯一值
AFTER指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器