SQL SERVER之触发器

触发器:
一种对表更改时自动执行SQL代码块的对象,普通的存储过程需要通过EXEC命令来调用存储过程,
–触发器则不需要,可以根据事件(插入、更新、删除)自动执行(触发机制)
–触发器创建时,会生成两张临时表(DELETED、INSERTED)
– FOR:和执行操作同时触发
– AFTER:执行之后触发,无约束权限
– INSTEAD OF:带约束权限触发器
–触发器(带事务):默认是一个完整的逻辑单元,不可分割。
–创建触发器:添加新产品的触发器

DELETED:该表存放了在执行删除或更新操作后所受影响的记录.
在执行DELETE或UPDATE操作时,被删除的记录会被移动到DELETE表中
INSERTED:该表存放了在执行插入或更新操作后影响的记录

在执行INSERT或UPDATE操作时,新的记录会被同时添加到触发器的表的INSERT表中

触发器主要有以下几个优点:
1.触发器是自动执行的,一旦设立就存在一种触发机制,永远监控着数据库的事件状态。
2.触发器可以对数据库中的表进行层叠更改。
3.触发器可与设置比CHECK更为复杂的约束限制,触发器还可以对不同表中的列进行引用

触发器是一种特殊的存储过程,创建的语法结构和存储过程类似,

基本语法格式:
CREATE TRIGGER [schema_name] trigger_name
ON {table|view}
{WITH <dml_trigger_option> [,…n]}
{FOR|AFTER|INSTANCE OF}{ [INSERT] {,} [UPDATE] [,] [DELETE] }
{WITH APPEND}
[NOT FOR REPLICATION]
AS
{sql_statement [;] [,…n] [EXTERNAL NAME <method specifir [;]>]}

trigger_name:触发器的名称
table|view:指定执行触发器的表或视图
AFTER:用于指定触发器只有在SQL语句中指定数据操作完成后才能被触发,有关级联操作和约束性检查也成功后才能执行触发器。AFTER关键字没有指定,AFTER就为默认值,该类型的触发器只能创建在表上,视图上不能创建。
INSTANCE OF:是一种动作执行前的触发类型。用触发器代替触发语句进行操作。在表或视图中只能定义一个INSTANCE OF触发器,可以定义多个AFTER触发器。
{[INSERT][,][UPDATE][,][DELETE]}:用于指定数据库在执行哪种数据操作事件响应触发器可以一次指定多个关键字,用逗号隔开。
AS:触发器要执行的操作
sql_statement:指定触发器中执行T-SQL语句时的尝试,触发器可以包含任意数量和种类的T-SQL语句。

insert触发器:
Demo 01:

CREATE TRIGGER product_inserted 
ON Product
AFTER INSERT
AS 
BEGIN
 PRINT '添加了新产品'
END

测试代码:

INSERT INTO Product(ProductName,Price) VALUES('戴森（DYSON）台灯 CD06 Lightcycle Morph',$4490)

DELETE触发器:
返回被删除的记录信息

CREATE TRIGGER product_deleted 
ON Product 
AFTER DELETE
AS
BEGIN
  SELECT ProductName,Price FROM DELETED
END

测试代码:

DELETE FROM Product WHERE ProductNo=2007

UPDATE触发器:当用户执行UPDATE语句时被调用:
更改指定的记录的产品名称,获取更新前后的数据

CREATE TRIGGER product_updated 
ON Product 
AFTER UPDATE 
AS
BEGIN
 SELECT ProductName AS '更新前的产品名称',Price FROM DELETED
 SELECT ProductName AS '更新后的产品名称',Price FROM INSERTED
END

测试代码:

UPDATE Product SET ProductName='Oppo Find X2 Pro' WHERE ProductNo=1020
--使用AFTER触发器首先会建立INSTERED和DELETED表,然后执行SQL语句中的数据操作
--最后才会执行触发器中的代码

INSTEAD OF 触发器:
当添加新产品时,如果价格大于100000拒绝添加

CREATE TRIGGER product_insertedcheck
ON Product
INSTEAD OF INSERT
AS
BEGIN
DECLARE @price MONEY
SELECT @price=Price FROM inserted
IF @price>=100000
BEGIN
 RAISERROR('新产品价格大于10W元,不能添加!',16,1)
 ROLLBACK TRAN  --事务回滚
END
END 

--INSTEAD OF触发器:在建立INSERTED 和DELETED表后,直接执行触发器

--测试代码:
INSERT INTO Product(ProductName,Price) VALUES('BMW 跑车',520000)

注意:
如果执行了SQL语句的数据操作,并不意味着已经提交到了数据库（持久化操作）–隐式事务
–有可能有触发器被触发,在触发器中抛出异常或者进行了显式的事务回滚,导致没有提交到数据库中

使用触发器:限制对保护数据的操作

CREATE TRIGGER product_limitupdated 
ON Product 
AFTER UPDATE
AS
BEGIN
 --update关键字,检测字段是否被更新过
  IF UPDATE(Price) AND EXISTS(SELECT * FROM inserted WHERE ProductNo=1018)
  BEGIN
  RAISERROR('产品编号为1018的价格不能被更改!',16,1) --抛出异常
  ROLLBACK TRAN --回滚事务
  END
END

--测试代码:
UPDATE Product SET Price=8956 WHERE ProductNo=1018

实现级联操作:
通过触发器对有关系(主外键)的表进行级联操作(级联更新、级联删除)

删除台灯产品类型,级联删除相关的Product子记录

CREATE TRIGGER trigproductdelete 
ON ProductType
INSTEAD OF  DELETE  --INSTEAD OF:带约束权限:执行顺序问题,
AS
BEGIN
 DECLARE @typeId INT
 SELECT @typeId=TypeId FROM DELETED

 DELETE FROM Product WHERE TypeId=@typeId  --先删除所有相关的子记录,才能删除对应的父记录
 DELETE FROM ProductType WHERE TypeId=@typeId --删除父记录
END

--测试代码：
DELETE ProductType WHERE TypeId=4
SELECT * FROM ProductType

使用存储过程查看触发器:

sp_helptext trigproductdelete  --trigproductdelete为触发器名称
sp_help trigproductdelete 

禁用和启用触发器:
触发器一旦被创建完成后便处于监听状态,只要触发数据操作(增删改),触发器就会被触发。

DISABLE TRIGGER trigproductdelete,ProductType_Delete  ON ProductType
ENABLE TRIGGER trigproductdelete,ProductType_Delete  ON ProductType