触发器

认识触发器：

       触发器是一种特殊的存储过程，它不能被显示的调用，而是在往表中插入记录，更改记录或者删除记录时，被自动激活。在触发器中可以查询其他表，也可以执行更复杂的T-SQL语句。如果执行的T-SQL语句执行了一个非法操作，则可以通过回滚事务使语句不能执行，并返回到事务执行前的状态，Microsoft SQL Server 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。

触发器的作用：

◎触发器可以对数据库进行级联修改

◎触发器可以完成比CKECK约束更复杂的限制

◎触发器可以发现改变前后表中数据的不同，并根据这些不同来进行相应的操作。

◎对于一个表上的不同操作（INSERT,UPDATE或者DELETE）可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器完成不同的操作。

◎完成特定的业务规则。

 

 

---

语法：
  CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}

---

AFTER
指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
如果仅指定 FOR 关键字，则 AFTER 是默认设置。
不能在视图上定义 AFTER 触发器。
INSTEAD OF
指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。
在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。
INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。
对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。

---

例1：

在Goods表中建立删除触发器，实现Goods表和Orders表中的级联删除。(注：这个功能在SQLSEVER2000以前只能用触发器完成)

 

CREATE TRIGGER GoodsDelete
ON Goods    --在其上执行触发器

AFTER DELETE
AS
DELETE FROM Orders
WHERE GoodsName IN
(SELECT Name FORM deleted)

---

例2：在Orders表上建立一个AFTER触发器，当向Orders表中插入一行（加入一条订单记录）时，检查订单中的货物是否在整理中（查看对应货物在Goods表中的状态是否为1），如果在整理中，则不能下订单。

CREATE TRIGGER OrderInsert
ON Orders
AFTER INSERT
AS
IF(SELECT Status FROM Goods,Insertd WHERE Goods.Name=inserted.Name)=1
BEGIN
   PRINT '货物正在整理中，不能在Orders表中添加该货物记录。'
   ROLLBACK TRANSACTION
END

---

例3：在Orders表上建立一个插入触发器，在添加一个订单时，减少Goods表相应货品记录的库存量

CREATE TRIGGER OrdersInsert_1
ON Orders
AFTER INSERT
AS
UPDATE Goods SET Storage=Storage-inserted.Quantity
FROM Goods,inserted
WHERE Goods.Name=inserted.GoodsName

 

---

例4 限定Orders表的订单日期（OrderDate)不能手工修改。

CREATE TRIGGER OrderDataUpDat(e
ON Orders
AFTER UPDATE
IF UPDATE(OrderDate)
BEGIN
RAISERROR('不能手动修改',10,1)
ROLLBACK TRANSACTION
END

 

---

例5：判断要插入的订单中的货物名称在Goods表中是否存在，如果不存在则回滚事务.

 

CREATE TRIGGER OrderInsert_2
ON Orders
AFTER INSERT
AS
IF (SELECT COUNT(*) FORM Goods,inserted
WHERE Goods.Name=inserted.GoodName)=0
BEGIN
PRINT('没有这种货物')
ROLLBACK TRAINSACTION
END

---

例6：INSTEAD OF触发器用于替代引起触发器执行的T-SQL语句。每当向SimpleCustomers视图执行INSERT语句时，Insertcustmer触发器被触发，这时Inserted表中已经有了要插入的数据，在Insertcustmer触发器的代码中，分析插入客户姓名的字符串，将其分拆为姓和名两个字符串，并插入Customers表,而原来的INSERT语句不能执行。

创建SimpleCustomers视图

通过SimpleCustomers视图向Customers表中添加记录

GO
CREATE VIEW SimpleCustomers(CustomerName,city,Tel)
AS
SELECT FistName+','+LastName,City,Tel
FROM Customers
GO
CREATE TIRGGER Insertcustmer
ON SimpleCustomers
INSTEAD OF INSERT
AS
DECLARE @Name varchar(40)
DECLARE @FistName varchar(20)
DECLARE @LastName varchar(20)
DECLARE @City varchar(20)
DECLARE @Tel varchar(20)
DECLARE @idx int
BEGIN
SELECT @Name=CustomerName,@City=City,@Tel=Tel
FROM inserted
SET @idx=CHARINDEX(',',@Name)
SET @FistName=LEFT(@Name,@idx-1)
SET @LastNme=RIGHT(@Name,@LEN(@Name)-@idx)
INSERT INTO Customers
(FistName,LastName,City,Tel)
VALUES(@FistName,@LastName,@City,@Tel)
END

---