SQL 触发器 示例

USE WebLogistics
IF exists(SELECT * FROM sysobjects where xtype='u' AND name='wareSave')
drop TABLE wareSave
go
IF exists(SELECT * FROM sysobjects where xtype='u' AND name='saleSave')
drop TABLE saleSave
go

--业务规则：销售金额=销售数量* 销售单价

CREATE table saleSave
(
Id int identity(1,1),
brand varchar(40) primary key not null,--品牌
demander varchar(40) null,--购货商
saleNum int null,--销售数量
salePrice money null,--销售单价
saleMoney money null,--销售金额
)
GO

--业务规则：库存金额=库存数量*库存单价

CREATE table wareSave
(
Id int identity(1,1),
brand varchar(40) primary key not null,--品牌
wareNum int null,--库存数量
warePrice money null,--库存单价
wareMoney money null--库存金额
)

--创建触发器1
/*
当向wareSave中插入多条数据时，会自动更新库存金额（根据业务规则）
*/
IF exists(SELECT * from sysobjects where xtype='tr' AND name='trigger_insert_wareSave')
drop TRIGGER trigger_insert_wareSave
GO

CREATE trigger trigger_insert_wareSave
on wareSave
for insert
as
declare @Id int;
declare @warenum money;
declare @wareprice money;
begin tran
declare ware_cursor cursor
for
select Id,wareNum,warePrice from INSERTED
open ware_cursor

fetch next from ware_cursor into @Id,@warenum,@wareprice

while @@fetch_status=0
begin
update wareSave SET wareMoney=@warenum*@wareprice
from wareSave ws
where ws.Id=@Id
fetch next from ware_cursor into @Id,@warenum,@wareprice
end
close ware_cursor
deallocate ware_cursor
commit tran
GO

--测试数据VALUES('123',10,10,10)
INSERT INTO wareSave
SELECT '红塔山新势力',100,12,0 UNION ALL
SELECT '红塔山人为峰',100,22,NULL UNION ALL
SELECT '云南映像',100,60,0 UNION ALL
SELECT '玉溪',0,30,0
--查看库存
SELECT * FROM wareSave
--DELETE from wareSave

--创建触发器2
/*
当向saleSave表中插入一条记录时引发，该表wareSave中的记录
*/
GO

IF exists(SELECT * from sysobjects where xtype='tr' AND name='trigger_insert_saleSave')
drop TRIGGER trigger_insert_saleSave
go

create trigger trigger_insert_saleSave
on saleSave
for insert
as
begin tran
--检查销售的商品是否存在
IF not exists(SELECT * from wareSave where brand IN(SELECT brand from INSERTED))
begin
raiserror('该卷烟不存在于库存',16,1);
rollback;
return;
end
--检查销售的商品是否有库存
IF exists(SELECT wareNum FROM wareSave where brand IN(SELECT brand from INSERTED) AND wareNum<=0)
begin
raiserror('该卷烟库存小于等于0，不能销售',16,1)
rollback;
return;
end
--检查销售的商品库存是否满足
declare @num int;
SELECT @num=wareNum from wareSave where brand IN(SELECT brand from INSERTED) AND wareNum<=any(SELECT saleNum FROM INSERTED)
IF exists(SELECT * from wareSave where brand IN(SELECT brand from INSERTED) AND wareNum<=any(SELECT saleNum FROM INSERTED))
begin
print '只能销售'+convert(varchar(10),@num);
raiserror('该卷烟库存不足,只能销售',16,1);
rollback;
return;
end

--如果数据合法
--更新saleSave表
update saleSave set saleMoney=i.saleNum*i.salePrice
from saleSave ss,INSERTED i
where ss.Id in(SELECT Id from INSERTED)

--更新wareSave表
declare @brand varchar(40);
declare @salenum int;
declare @saleprice money;
declare @wareprice money;
select @brand=brand,@salenum=saleNum,@saleprice=salePrice from INSERTED;
select @wareprice=warePrice from wareSave where brand=@brand;
update wareSave SET wareNum=wareNum-@salenum,wareMoney=(wareNum-@salenum)*warePrice
where brand=@brand
commit tran
GO

INSERT saleSave VALUES('红塔山新势力','需求商1',100,10,2)

SELECT * FROM saleSave

SELECT * FROM wareSave

DELETE FROM saleSave