表1:CProStardOut ,字段如下
PrintID DXUnit
1 AAA
2 BBB
3 CCC
4 DDD
5 EEE
表2:CproGoldOutDetail 字段如下
PrintID ProdNo
1 001
2 002
3 003
4 004
5 005 表3:CProGoldBar 字段如下
ProdNo IsOut
001 1
002 1
003 1
004 1
005 1我想在表1:CProStardOut中写个触发器,让表1:CProStardOu删除PrintID 为1的时候
将表3:CProGoldBar 中ProdNo字段中为001的IsOut字段的值修改成0;
三个表的关系很简单,表1的PrintID关联表2的PrintID,表2的ProdNo关联表三的ProdNo;
PrintID DXUnit
1 AAA 表2:CproGoldOutDetail 字段如下 PrintID ProdNo
1 001
2 002 表3:CProGoldBar 字段如下 ProdNo IsOut
001 1
002 1
create trigger triggername
on CProStardOut
for delete
as
update c.isout = 0
from cprogoldbar c
left join cprogoldoutdetail b on c.prodno = b.prodno
left join deleted a on a.PrintID = b.PrintID
end
create trigger triggername on CProStardOut for delete
as
update CProGoldBar set isout = 0 from cprogoldbar c
left join cprogoldoutdetail b on c.prodno = b.prodno
left join deleted a on a.PrintID = b.PrintID
end
(
PrintID int,
DXUnit varchar(10)
)
Go
create table CproGoldOutDetail
(
PrintID int,
ProdNo varchar(10)
)
Go
create table CProGoldBar
(
ProdNo varchar(10),
IsOut int
)
Goinsert CProStardOut select 1,'AAA'
insert CProStardOut select 2,'BBB'
insert CProStardOut select 3,'CCC'
insert CProStardOut select 4,'DDD'
insert CProStardOut select 5,'EEE'
Goinsert CproGoldOutDetail select 1,'001'
insert CproGoldOutDetail select 2,'002'
insert CproGoldOutDetail select 3,'003'
insert CproGoldOutDetail select 4,'004'
insert CproGoldOutDetail select 5,'005'
Goinsert CProGoldBar select '001',1
insert CProGoldBar select '002',1
insert CProGoldBar select '003',1
insert CProGoldBar select '004',1
insert CProGoldBar select '005',1
GoCREATE TRIGGER TEMP_SC ON CProStardOut
AFTER delete
as
update CProGoldBar set IsOut=0
from deleted D,
CproGoldOutDetail A1,
CProGoldBar B1
where D.PrintID=A1.PrintID and A1.ProdNo=B1.ProdNo
Godelete from CProStardOut where PrintID=1
Goselect * from CProGoldBar
Go
for delete
as
update a
set IsOut=0
from
CProGoldBar a
join
CproGoldOutDetail b on a.ProdNo =b.ProdNo
join
deleted c on c.PrintID =b.PrintID
on CProStardOut
for delete
as
update c
set c.isout = 0
from cprogoldbar c
left join cprogoldoutdetail b on c.prodno = b.prodno
left join deleted a on a.PrintID = b.PrintID
end