sql server2005触发器问题
发布网友
发布时间:2022-05-06 21:01
我来回答
共2个回答
热心网友
时间:2022-05-06 22:31
CREATE TRIGGER trigger1 ON zw_bkd
WITH EXECUTE AS CALLER
AFTER UPDATE
AS
BEGIN
declare @oldshr int,@newshr int, @gsdm int,@kjnd int,@je int,@zbid int
--这里视你的情况修改变量类型
if update(shr)
begin
select @oldshr=Deleted.shr from Deleted --@oldshr是更新前的shr
select @newshr=Inserted.shr, --@newshr是更新后的shr
@gsdm=Inserted.gsdm,@kjnd=Inserted.kjnd,
@je=Inserted.je,@zbid=Inserted.zbid
from Inserted
if @oldshr is null and @newshr is not null --如果shr从空变为非空
begin
--如果zw_ed 中存在zbid与zw_bkd相同的行,删除这些行
if (select count(*) from zw_ed where zw_ed.zbid=@zbid)!=0
delete from zw_ed where zw_ed.zbid=@zbid
--将zw_bkd里的gsdm,kjnd,je,zbid插入到zw_ed表里
insert into zw_ed (gsdm,kjnd,je,zbid) values (@gsdm,@kjnd,@je,@zbid)
end
else if @oldshr is not null and @newshr is null --如果shr从非空变为空
begin
--删除zw_ed 中zbid与zw_bkd相同的行
delete from zw_ed where zw_ed.zbid=@zbid
end
end
END