--建表table1
create table table1(ID int,姓名 varchar(4),性别 varchar(4),年龄 int)
go
insert into table1
select 1, 'AAA', '男', 30 union all
select 2, 'BBB', '女', 25--建表table2
create table table2(ID int,姓名 varchar(4),性别 varchar(4))
go
insert into table2
select 1, 'AAA', '男' union all
select 2, 'BBB', '女'--建立触发器
create trigger Insert_trig
on table1
for insert
as
insert into table2 select [ID],姓名,性别 from insertedgo
create trigger Update_trig
on table1
for update
as
update table2
set table2.[姓名]=inserted.[姓名],table2.[性别]=inserted.[性别]
from table2,inserted
where table2.[ID]=inserted.[ID]
go
create trigger Delete_trig
on table1
for delete
as
delete table2 from deleted,table2
where deleted.[ID]=table2.[ID]go
create table table1(ID int,姓名 varchar(4),性别 varchar(4),年龄 int)
go
insert into table1
select 1, 'AAA', '男', 30 union all
select 2, 'BBB', '女', 25--建表table2
create table table2(ID int,姓名 varchar(4),性别 varchar(4))
go
insert into table2
select 1, 'AAA', '男' union all
select 2, 'BBB', '女'--建立触发器
create trigger Insert_trig
on table1
for insert
as
insert into table2 select [ID],姓名,性别 from insertedgo
create trigger Update_trig
on table1
for update
as
update table2
set table2.[姓名]=inserted.[姓名],table2.[性别]=inserted.[性别]
from table2,inserted
where table2.[ID]=inserted.[ID]
go
create trigger Delete_trig
on table1
for delete
as
delete table2 from deleted,table2
where deleted.[ID]=table2.[ID]go
create table table1(ID int,姓名 varchar(4),性别 varchar(4),年龄 int)
go
insert into table1
select 1, 'AAA', '男', 30 union all
select 2, 'BBB', '女', 25--建表table2
create table table2(ID int,姓名 varchar(4),性别 varchar(4))
go
insert into table2
select 1, 'AAA', '男' union all
select 2, 'BBB', '女'--建立触发器
create trigger Insert_trig
on table1
for insert
as
insert into table2 select [ID],姓名,性别 from insertedgo
create trigger Update_trig
on table1
for update
as
update table2
set table2.[姓名]=inserted.[姓名],table2.[性别]=inserted.[性别]
from table2,inserted
where table2.[ID]=inserted.[ID]
go
create trigger Delete_trig
on table1
for delete
as
delete table2 from deleted,table2
where deleted.[ID]=table2.[ID]go
你说的情况是table1,table2在同一数据库,我现在的情况是table1,table2不在同一数据库。
use Northwind
go--创建新增同步的触发器
create trigger tr_insert on table1
for insert
as
set identity_insert pubs..table2 on
insert pubs..table2(id,姓名,性别)
select id,姓名,性别 from inserted
go--创建删除同步的触发器
create trigger tr_delete on table1
for delete
as
delete a
from pubs..table2 a where exists(select 1 from deleted where id=a.id)
go--创建更新同步的触发器
create trigger tr_update on table1
for update
as
update a set 姓名=i.姓名,性别=i.性别
from pubs..table2 a join inserted i on a.id=i.id
go