A表数据有
订单号、商品名称
B表数据有
订单号、运费
我想在B表里创建一个触发器实现
如果A表的订单号=B表的订单号
那么就将满足上面条件的订单号、商品名称、运费全部插入进C表
请问该操作的触发器该如何写
订单号、商品名称
B表数据有
订单号、运费
我想在B表里创建一个触发器实现
如果A表的订单号=B表的订单号
那么就将满足上面条件的订单号、商品名称、运费全部插入进C表
请问该操作的触发器该如何写
调试欢乐多
as
delete c from b,c where b.订单号=c.订单号
insert into c select a.*,b.运费 from a,b
where a.订单号=b.订单号
for insert,update
as
begin
insert c
select a.订单号,商品名称,运费
from inserted i join a on i.订单号=a.订单号
end
for insert
as
begin
if(select * from A where 订单号 exists(select 订单号 from inserted)>0)
begin
insert into C(订单号,商品名称,运费) select i.订单号,i.商品名称,i.运费 from inserted i
end
end
for insert
as
begin
if(select * from A where 订单号 exists(select 订单号 from inserted)>0)
begin
insert into C(订单号,商品名称,运费) select i.订单号,i.商品名称,i.运费 from inserted i
end
end
for insert,update,delete
as
delete
c
from
b,c
where
b.订单号=c.订单号
insert into
c
select
a.*,b.运费
from
a,b
where a.订单号=b.订单号
as
if not exists(select 1 from inserted)
delete c from deleted t where c.订单号 = t.订单号
else if not exists(select 1 from deleted)
insert into c(订单号,商品名称,运费) select t.订单号 , a.商品名称 , t.运费 from inserted t , a where t.订单号 = a.订单号
else
update c set 运费 = 运费 + t.运费 from inserted t where c.订单号 = t.订单号
go
create trigger tr_Info on b for insert
as
begin
if(select * from A where 订单号 exists(select 订单号 from inserted)>0)
insert into C(订单号,商品名称,运费) select i.订单号,i.商品名称,i.运费 from inserted i
end
for insert
as
begin
if(select * from A where 订单号 exists(select 订单号 from inserted)>0)
begin
insert into C(订单号,商品名称,运费) select i.订单号,i.商品名称,i.运费 from inserted i
end
end
--最好c表字段也列出来一一对应
insert into c(**,**,**,**,**...) select a.*,b.运费 from a,b
where a.订单号=b.订单号