触发器.txt

这个是我的数据库的课程设计:超市商品数据管理系统的设计,,采用面向C/S模式的通用开发工具Delphi设计其前台的操作

附录
1、库存表中的更新触发器（更新超高超低表chao_caizepeng042_04）
REATE TRIGGER [4_caizepeng042_04] ON [dbo].[kucun_caizepeng042_04] 
FOR UPDATE
AS
if exists(select chao_caizepeng042_04.商品编号 from dinge_caizepeng042_04,chao_caizepeng042_04,inserted where  inserted.商品编号=dinge_caizepeng042_04.商品编号 and dinge_caizepeng042_04.商品编号=chao_caizepeng042_04.商品编号)
begin
update chao_caizepeng042_04 set 库存总量=库存总量+convert(int,inserted.数量) from chao_caizepeng042_04, inserted where chao_caizepeng042_04.商品编号=inserted.商品编号
update chao_caizepeng042_04 set 库存总量=库存总量-convert(int,deleted.数量) from chao_caizepeng042_04, deleted where chao_caizepeng042_04.商品编号=deleted.商品编号
update chao_caizepeng042_04 set 是否超高储额='是',是否超低储额='否' from inserted,deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and inserted.商品编号=deleted.商品编号 and c.库存总量>=d.最高储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='是' from inserted,deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and inserted.商品编号=deleted.商品编号 and c.库存总量<=d.最低储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='否' from inserted,deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and inserted.商品编号=deleted.商品编号 and c.库存总量>d.最低储备额 and c.库存总量<d.最高储备额
end
else
begin
insert into chao_caizepeng042_04 select dinge_caizepeng042_04.商品编号,0,'否','否' from dinge_caizepeng042_04,(select 商品编号 from deleted 
union 
select 商品编号 from inserted) as i where dinge_caizepeng042_04.商品编号=i.商品编号
update chao_caizepeng042_04 set 库存总量=库存总量+convert(int,inserted.数量) from chao_caizepeng042_04, inserted where chao_caizepeng042_04.商品编号=inserted.商品编号
update chao_caizepeng042_04 set 库存总量=库存总量-convert(int,deleted.数量) from chao_caizepeng042_04, deleted where chao_caizepeng042_04.商品编号=deleted.商品编号

update chao_caizepeng042_04 set 是否超高储额='是',是否超低储额='否' from inserted,deleted,chao_caizepeng042_04,dinge_caizepeng042_04 where inserted.商品编号=dinge_caizepeng042_04.商品编号 and chao_caizepeng042_04.商品编号=dinge_caizepeng042_04.商品编号 and inserted.商品编号=deleted.商品编号 and chao_caizepeng042_04.库存总量>=dinge_caizepeng042_04.最高储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='是' from inserted,deleted,chao_caizepeng042_04,dinge_caizepeng042_04 where inserted.商品编号=dinge_caizepeng042_04.商品编号 and chao_caizepeng042_04.商品编号=dinge_caizepeng042_04.商品编号 and inserted.商品编号=deleted.商品编号 and chao_caizepeng042_04.库存总量<=dinge_caizepeng042_04.最低储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='否' from inserted,deleted,chao_caizepeng042_04,dinge_caizepeng042_04 where inserted.商品编号=dinge_caizepeng042_04.商品编号 and chao_caizepeng042_04.商品编号=dinge_caizepeng042_04.商品编号 and inserted.商品编号=deleted.商品编号 and chao_caizepeng042_04.库存总量>dinge_caizepeng042_04.最高储备额 and chao_caizepeng042_04.库存总量<dinge_caizepeng042_04.最低储备额
end

2、库存表中的删除触发器（更新超高超低表chao_caizepeng042_04）
CREATE TRIGGER [6_caizepeng042_04] ON [dbo].[kucun_caizepeng042_04] 
FOR DELETE 
AS
if exists(select chao_caizepeng042_04.商品编号 from dinge_caizepeng042_04,chao_caizepeng042_04,deleted where  deleted.商品编号=dinge_caizepeng042_04.商品编号 and dinge_caizepeng042_04.商品编号=chao_caizepeng042_04.商品编号)
begin
update chao_caizepeng042_04 set 库存总量=库存总量-convert(int,deleted.数量) from chao_caizepeng042_04, deleted where chao_caizepeng042_04.商品编号=deleted.商品编号
update chao_caizepeng042_04 set 是否超高储额='是',是否超低储额='否' from deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where deleted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>=d.最高储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='是' from deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where deleted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量<=d.最低储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='否' from deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where deleted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>d.最低储备额 and c.库存总量<d.最高储备额
end
else
begin
insert into chao_caizepeng042_04 select dinge_caizepeng042_04.商品编号,0,'否','否' from dinge_caizepeng042_04,deleted where dinge_caizepeng042_04.商品编号=deleted.商品编号
update chao_caizepeng042_04 set 库存总量=(select sum(kucun_caizepeng042_04.数量) from kucun_caizepeng042_04,deleted   where kucun_caizepeng042_04.商品编号=deleted.商品编号 group by kucun_caizepeng042_04.商品编号) from deleted,chao_caizepeng042_04 where deleted.商品编号=chao_caizepeng042_04.商品编号
update chao_caizepeng042_04 set 是否超高储额='是',是否超低储额='否' from deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where deleted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>=d.最高储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='是' from deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where deleted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量<=d.最低储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='否' from deleted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where deleted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>d.最低储备额 and c.库存总量<d.最高储备额
end
3、库存表中的插入触发器（更新超高超低表chao_caizepeng042_04）
CREATE TRIGGER [5_caizepeng042_04] ON [dbo].[kucun_caizepeng042_04] 
FOR INSERT
AS
if exists(select chao_caizepeng042_04.商品编号 from dinge_caizepeng042_04,chao_caizepeng042_04,inserted where  inserted.商品编号=dinge_caizepeng042_04.商品编号 and dinge_caizepeng042_04.商品编号=chao_caizepeng042_04.商品编号)
begin
update chao_caizepeng042_04 set 库存总量=库存总量+convert(int,inserted.数量) from chao_caizepeng042_04, inserted where chao_caizepeng042_04.商品编号=inserted.商品编号
update chao_caizepeng042_04 set 是否超高储额='是',是否超低储额='否' from inserted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>=d.最高储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='是' from inserted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量<=d.最低储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='否' from inserted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>d.最低储备额 and c.库存总量<d.最高储备额
end
else
begin
insert into chao_caizepeng042_04 select dinge_caizepeng042_04.商品编号,0,'否','否' from dinge_caizepeng042_04,inserted where dinge_caizepeng042_04.商品编号=inserted.商品编号
update chao_caizepeng042_04 set 库存总量=(select sum(kucun_caizepeng042_04.数量) from kucun_caizepeng042_04,inserted where kucun_caizepeng042_04.商品编号=inserted.商品编号  group by kucun_caizepeng042_04.商品编号) from chao_caizepeng042_04,inserted where  chao_caizepeng042_04.商品编号=inserted.商品编号
update chao_caizepeng042_04 set 库存总量=库存总量-convert(int,deleted.数量) from chao_caizepeng042_04, deleted where chao_caizepeng042_04.商品编号=deleted.商品编号
update chao_caizepeng042_04 set 是否超高储额='是',是否超低储额='否' from inserted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>=d.最高储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='是' from inserted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量<=d.最低储备额
update chao_caizepeng042_04 set 是否超高储额='否',是否超低储额='否' from inserted,chao_caizepeng042_04 c,dinge_caizepeng042_04 d where inserted.商品编号=d.商品编号 and c.商品编号=d.商品编号 and c.库存总量>d.最低储备额 and c.库存总量<d.最高储备额
end
4、销售表中的插入触发器（更新超高超低表chao_caizepeng042_04）
CREATE TRIGGER [9_caizepeng042_04] ON [dbo].[sell_caizepeng042_04] 
FOR INSERT
AS