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;
CREATE TRIGGER TR_InvoiceItem_AfterInsert_ForRedundancy_Invoice_TotalAmount ON InvoiceItem
AFTER INSERT 
    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;
判断 ROWCOUNT_BIG 是有必要的, 因为即便是没有 row 修改 trigger 也会被触发. 如果没有 return 就有可能出现递归死循环.
监听 instead of, 做级联删除
DROP TRIGGER TR_Invoice_InsteadOfDelete_ForCascade_InvoiceItem;
CREATE TRIGGER TR_Invoice_InsteadOfDelete_ForCascade_InvoiceItem ON Invoice
INSTEAD OF DELETE 
    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;
列出所有 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;