包装信息表 gbaozhuangxingxi
包装ID 包装代码 数量 单位
1 ZX-10-10-1-CPT 20 件/箱
2 ZX-10-10-2-CPT 30 件/箱
3 ZX-10-10-3-CPT 40 件/箱
4 ZX-10-10-4-CPT 50 件/箱
包装计划表Gbaozhuangjihua
包装计划ID 包装代码 计划数 包装ID
1 ZX-10-10-1-CPT 5 1
2 ZX-10-10-2-CPT 6 2
3 ZX-10-10-3-CPT 7 3
4 ZX-10-10-4-CPT 8 4
高手些:当修包 计划表(gbaozhuangjihua)中的包装代码时,查询包装信息表(gbaozhuangxingxi)中的包装代码是否存在,若存在,修改当前包装代码,并修改对应的包装信息表中的包装ID查出来替换包装计划表中的包装ID,若不存在,提示包装代码表中不存在相应代码信息,用触发器怎么实现,谢谢大家! 如,我想替换包装信息表中的第三条记录,将ZX-10-10-3-CPT,将他替换成ZX-10-10-2-CPT,它此时就要执行查询包装ZX-10-10-2-CPT 在包装信息表中是否存在,若存在,就替换当前包装代码为ZX-10-10-2-CPT,并将ID改成2,结果如下
包装计划表Gbaozhuangjihua
包装计划ID 包装代码 计划数 包装ID
1 ZX-10-10-1-CPT 5 1
2 ZX-10-10-2-CPT 6 2
3 ZX-10-10-2-CPT 7 2
4 ZX-10-10-4-CPT 8 4
包装ID 包装代码 数量 单位
1 ZX-10-10-1-CPT 20 件/箱
2 ZX-10-10-2-CPT 30 件/箱
3 ZX-10-10-3-CPT 40 件/箱
4 ZX-10-10-4-CPT 50 件/箱
包装计划表Gbaozhuangjihua
包装计划ID 包装代码 计划数 包装ID
1 ZX-10-10-1-CPT 5 1
2 ZX-10-10-2-CPT 6 2
3 ZX-10-10-3-CPT 7 3
4 ZX-10-10-4-CPT 8 4
高手些:当修包 计划表(gbaozhuangjihua)中的包装代码时,查询包装信息表(gbaozhuangxingxi)中的包装代码是否存在,若存在,修改当前包装代码,并修改对应的包装信息表中的包装ID查出来替换包装计划表中的包装ID,若不存在,提示包装代码表中不存在相应代码信息,用触发器怎么实现,谢谢大家! 如,我想替换包装信息表中的第三条记录,将ZX-10-10-3-CPT,将他替换成ZX-10-10-2-CPT,它此时就要执行查询包装ZX-10-10-2-CPT 在包装信息表中是否存在,若存在,就替换当前包装代码为ZX-10-10-2-CPT,并将ID改成2,结果如下
包装计划表Gbaozhuangjihua
包装计划ID 包装代码 计划数 包装ID
1 ZX-10-10-1-CPT 5 1
2 ZX-10-10-2-CPT 6 2
3 ZX-10-10-2-CPT 7 2
4 ZX-10-10-4-CPT 8 4
create trigger tg_tb on Gbaozhuangjihua
after insert,update
as
begin
update a
set a.包装ID = b.id
from Gbaozhuangjihua a join gbaozhuangxingxi b on a.包装代码 = b.包装代码
where b.id <> a.包装ID
end
create table gbaozhuangxingxi
(包装ID int, 包装代码 varchar(20), 数量 int, 单位 varchar(10))insert into gbaozhuangxingxi
select 1, 'ZX-10-10-1-CPT', 20, '件/箱' union all
select 2, 'ZX-10-10-2-CPT', 30, '件/箱' union all
select 3, 'ZX-10-10-3-CPT', 40, '件/箱' union all
select 4, 'ZX-10-10-4-CPT', 50, '件/箱'create table Gbaozhuangjihua
(包装计划ID int, 包装代码 varchar(20), 计划数 int, 包装ID int)insert into Gbaozhuangjihua
select 1, 'ZX-10-10-1-CPT', 5, 1 union all
select 2, 'ZX-10-10-2-CPT', 6, 2 union all
select 3, 'ZX-10-10-3-CPT', 7, 3 union all
select 4, 'ZX-10-10-4-CPT', 8, 4 create trigger tr_Gbaozhuangjihua
on Gbaozhuangjihua
for update
as
begin
if update(包装代码)
begin
if exists(select 1 from gbaozhuangxingxi a
inner join inserted b on a.包装代码=b.包装代码)
update a set a.包装ID=
(select a.包装ID from gbaozhuangxingxi a
inner join inserted b on a.包装代码=b.包装代码)
from Gbaozhuangjihua a
inner join inserted b on a.包装计划ID=b.包装计划ID
else
begin
print '包装代码表中不存在相应代码信息.'
rollback tran
end
end
end
-- 测试更新为 ZX-10-10-2-CPT
update Gbaozhuangjihua set 包装代码='ZX-10-10-2-CPT' where 包装计划ID=3select * from Gbaozhuangjihua包装计划ID 包装代码 计划数 包装ID
----------- -------------------- ----------- -----------
1 ZX-10-10-1-CPT 5 1
2 ZX-10-10-2-CPT 6 2
3 ZX-10-10-2-CPT 7 2
4 ZX-10-10-4-CPT 8 4
-- 测试更新为不存在的包装代码
update Gbaozhuangjihua set 包装代码='abc' where 包装计划ID=3select * from Gbaozhuangjihua包装计划ID 包装代码 计划数 包装ID
----------- -------------------- ----------- -----------
1 ZX-10-10-1-CPT 5 1
2 ZX-10-10-2-CPT 6 2
3 ZX-10-10-2-CPT 7 2
4 ZX-10-10-4-CPT 8 4
对Gbaozhuangjihua的更新,也只针对被更新的那条,
原先包装ID是1,2,3,4, 现在是1,2,2,4