有3个表 A表 B表 C表
-------------- --------------- ---------------
编号 名称 编号 数量 编号 数量
1 大米 1 10 1 110
2 小米 2 20 2 210
3 玉米 3 30 3 30
现在想 使用 存储过程 或 触发器
当A表 增加1行或删除1行时 B C表 同时增加或删除 相应的行
-------------- --------------- ---------------
编号 名称 编号 数量 编号 数量
1 大米 1 10 1 110
2 小米 2 20 2 210
3 玉米 3 30 3 30
现在想 使用 存储过程 或 触发器
当A表 增加1行或删除1行时 B C表 同时增加或删除 相应的行
on a
for update
update b set .......where id =(select id from inserted)
update c set .......where id =(select id from inserted)
create trigger t_delete
on a
for delete
delete from b where id =(select id from deleted)
delete from c where id =(select id from deleted)
for update,insert,delete
as if exists(select * from deleted)
begin
delete from B where id in( select id from deleted)
delete from C where id in( select id from deleted)
end
if exists(select * from inserted)
begin
insert into B
select * from inserted
go;
insert into C
select * from inserted
end
insert a values(1,'大米')
insert a values(2,'xx')create table b(id int,num int)
insert b values(1,10)create table c (id int,num int)gocreate trigger trigger_A on A
for insert,delete
as
begin
delete b where id in (select id from deleted)
delete c where id in (select id from deleted)
insert b (id) select id from inserted
insert c (id) select id from inserted
end
create table a(id int,name varchar(20))
insert a values(1,'大米')
insert a values(2,'xx') create table b(id int,num int)
insert b values(1,10) create table c (id int,num int) go create trigger trigger_A on A
for insert,delete
as
begin
delete b where id in (select id from deleted)
delete c where id in (select id from deleted)
insert b (id) select id from inserted
insert c (id) select id from inserted
end太有菜了~~~~~