Sql Server 触发器

/*
SQL Server触发器
  1.什么是触发器
    触发器是一种特殊类型的存储过程，因为它和存储过程有一样的特征，都是预用写好的Sql命令存储在SqlServer服务器中。
	触发器在指定的表中的数据发生变化（insert/update/delete）的时候会自动执行。它与存储过程的区别也在此，因为存储过程是需要我们用命令去调用的。
    触发器一般用在比check约束更加复杂的约束上面。

	注意:触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

  2.触发器分类
    DML触发器和DDL触发器。

	DML触发器：
	当数据库中表中的数据发生变化时，包括insert,update,delete任意操作，如果我们对该表写了对应的DML触发器，那么该触发器将自动执行。
	DML触发器的主要作用在于强制执行业 务规则，以及扩展Sql Server约束，默认值等。因为我们知道约束只能约束同一个表中的数据，而触发器中则可以执行任意Sql命令。
    例子:我们想要在送货后减少库存的数量，那我们就可以在送货单的表上写update触发器，用来减少库存的数量。

	DDL触发器:
	它是Sql Server2005新增的触发器，主要用于审核与规范对数据库中表，触发器，视图等结构上的操作。比如在修改表，修改列，新增表，新增列等。
	它在数据库结构发生变化时执行，我们主要用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如不允许删除某些指定表等。

    DML触发器：
	   1.after触发器（之后触发）
	       a.insert触发器
		   b.update触发器
		   c.delete触发器

	   2.insert of 触发器 之前触发

	   after触发器只有在执行insert、update、delete之后才能触发，且只能定义在表上。
	   而insert of 触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义，也可在视图上定义。

  3.触发器有两个非常特殊，非常重要的两个表。
    insetred:插入表
	deleted:删除表
	这两个是逻辑表，也是虚表。由系统在内存中自动创建这两张表，这两张表不会存储在数据库中，而且这两张表都是只读的。这两张表的结果总是与被
	触发器应用的表的结构相同。当触发器完成后，这两表将会被删除。

	Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。


  4.编码实现
    业务逻辑:有水果销售表和水果库存表，
	   */
--创建表

create table FruitsSaleTable
(
FruitsName varchar(20) primary key not null,  --水果名称
Supplier varchar(50),  --供货商
SaleNumber int,  --销售数量
SaleUnitPrice money,  --单价
SaleTotalSum money   --销售总金额
)

insert into FruitsSaleTable(FruitsName,Supplier,SaleNumber,SaleUnitPrice,SaleTotalSum)
values('冰糖雪梨','B',20,10,200)

create table FruitsStock
(
FruitsName varchar(20) primary key not null,  --水果名称
StockNumber int,       --库存数量
StockUnitPrice money,  --库存单价
StockTotalSum money    --库存总金额
)


/*
Insert:
首先我们在库存表（FruitsStock）中创建一个Insert触发器，
目的：1.库存总金额=库存数量*库存单价 这个等式成立
      2.确保收货时 “库存数量”>0  不然收货动作将毫无意义
*/

create trigger I_insert_FruitsStock  --创建触发器
on FruitsStock   --作用那张表
for insert    --触发条件
as
--提交事务
begin transaction 

--判断收货库存是否>0
if not exists
(
select StockNumber from FruitsStock where FruitsName in (select FruitsName from inserted) and StockNumber >0
)
begin
--不满足条件，提示
raiserror('错误，收货数量不能小于0',16,1)

--事务回滚
rollback
return 
end

--条件满足，强制执行SQL，保证业务逻辑
update FruitsStock set StockTotalSum = StockNumber * StockUnitPrice 
where FruitsName in (select FruitsName from inserted)

--提交事务
commit transaction
go

--测试触发器
--1.数据都正确
insert into FruitsStock
(FruitsName,StockNumber,StockUnitPrice,StockTotalSum)
values('苹果',50,10,500)

/*
结果:OK
(1 行受影响)
*/

--2.库存总金额<>库存数量*库存单件
insert into FruitsStock
(FruitsName,StockNumber,StockUnitPrice,StockTotalSum)
values('冰糖雪梨',30,10,100)

/*
结果:OK
(1 行受影响)
查询结果可的：触发器自动进行计算，结果中显示正确的库存总金额
*/

--3.当收货数量为<=0时
insert into FruitsStock
(FruitsName,StockNumber,StockUnitPrice,StockTotalSum)
values('蓝莓',0,10,100)

/*
结果:OK
消息 50000，级别 16，状态 1，过程 I_insert_FruitsStock，第 138 行
错误，收货数量不能小于0
消息 3609，级别 16，状态 1，第 124 行
事务在触发器中结束。批处理已中止。
*/


/*
update:
首先我们在销售表（FruitsSaleTable）中创建一个update触发器，
业务逻辑：更改销售价格，销售总金额一起改变
*/

create trigger U_update_FruitsSaleTable
on FruitsSaleTable
for update 
as

if update(SaleUnitPrice)
declare @price money  --更改后的销售价格
--提交事务
begin transaction
select @price=SaleUnitPrice from  FruitsSaleTable where FruitsName in (select FruitsName from inserted)
--begin
--if(@price<=0)
-- print'价格修改错误'
-- rollback
-- return 
-- end
update FruitsSaleTable set SaleTotalSum =SaleNumber * @price where FruitsName in (select FruitsName from inserted)

commit transaction
go


/*
update触发器测试
*/
update FruitsSaleTable set SaleUnitPrice=7 where FruitsName='苹果'

/*
结果:
(1 行受影响)
*/


/*
delete触发器：
业务逻辑：删除库存表中数据时同时删除销售表
*/

create trigger D_delete_FruitsStock
on FruitsStock
for delete
as
begin
delete FruitsStock from FruitsStock fs,deleted d where fs.FruitsName=d.FruitsName
delete FruitsSaleTable from FruitsSaleTable st,deleted d where st.FruitsName=d.FruitsName
end
go



/*
delete触发器
(0 行受影响)

(1 行受影响)
*/

delete from FruitsStock where FruitsName='冰糖雪梨'

select * from FruitsStock

select * from FruitsSaleTable


/*
写的比较简单，本人也处于学习阶段，有错误的地方希望可以指点一下。
*/

　　

转载于:https://www.cnblogs.com/pushYYL/p/10001964.html