SQL Server – Trigger 触发器

参考: 

SQLServer触发器详解(概述、工作原理、应用)

CREATE TRIGGER (Transact-SQL)

SQL Server Triggers and Transactions

以前写过的笔记

 

介绍

触发器适合用在维护冗余. 它可以监听指定 table 的 insert, update, delete.

监听时机分 2 种, after 和 instead of

after 就是在数据变化之后触发. 在 trigger 中通过访问 inserted 和 deleted 就可以获取改动前后的数据.

inserted 和 deleted 是表结构哦, 有多个 row.

instead of 是替代原来的执行 (原来的执行就没有了哦, trigger 里面要自己实现), 它在数据还没有执行前触发.

 

关于事务

Trigger 在运行时是自带事务的, 哪怕原先的执行并没有开启事务. 

而 Isolation 级别默认是依据原先执行的事务, 但如果内部修改了 Isolation 当返回外部的时候, Isolation 会自动被调回去.

 

 

关于递归

一个 trigger 里面执行了另一些 insert, update, delete 会继续触发其它 trigger.

所以 trigger 是支持递归的, 需要自行控制避免死循环哦.

 

例子

监听 after insert, 更新冗余 (要记得 inserted 是表里面有多条数据, 要批量处理)

DROP TRIGGER TR_InvoiceItem_AfterInsert_ForRedundancy_Invoice_TotalAmount;
GO
CREATE TRIGGER TR_InvoiceItem_AfterInsert_ForRedundancy_Invoice_TotalAmount ON InvoiceItem
AFTER INSERT 
AS
    IF (ROWCOUNT_BIG() = 0) RETURN;
    SET NOCOUNT ON;
    UPDATE Invoice SET TotalAmount = (SELECT SUM(Subtotal) FROM InvoiceItem WHERE InvoiceId = Invoice.InvoiceId)
    FROM Invoice 
    INNER JOIN inserted ON Invoice.InvoiceId = inserted.InvoiceId;
GO

判断 ROWCOUNT_BIG 是有必要的, 因为即便是没有 row 修改 trigger 也会被触发. 如果没有 return 就有可能出现递归死循环.

监听 instead of, 做级联删除

DROP TRIGGER TR_Invoice_InsteadOfDelete_ForCascade_InvoiceItem;
GO
CREATE TRIGGER TR_Invoice_InsteadOfDelete_ForCascade_InvoiceItem ON Invoice
INSTEAD OF DELETE 
AS
    IF (ROWCOUNT_BIG() = 0) RETURN;
    SET NOCOUNT ON;

    DELETE InvoiceItem 
    FROM InvoiceItem 
    INNER JOIN deleted ON InvoiceItem.InvoiceId = deleted.InvoiceId;

    DELETE Invoice
    FROM Invoice 
    INNER JOIN deleted ON Invoice.InvoiceId = deleted.InvoiceId;
GO

 

列出所有 Trigger

SELECT T.[name], M.[definition], T.is_instead_of_trigger 
FROM sys.triggers T
LEFT JOIN sys.sql_modules M ON OBJECT_ID(T.[name]) = M.object_id
WHERE T.[type] = 'TR';

删除所有 Trigger

DECLARE @dropAllTrigger NVARCHAR(MAX) = N'';
SELECT @dropAllTrigger += 
    N'DROP TRIGGER IF EXISTS ' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + 
    QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers T
LEFT JOIN sys.sql_modules M ON OBJECT_ID(T.[name]) = M.object_id
WHERE T.[type] = 'TR';
PRINT @dropAllTrigger;