INSTEAD OF 触发器（DML）的用例

在 SQL Server 中构建与优化触发器

Florin Angelescu

Instructor

INSTEAD OF 触发器的通用用途

阻止某些操作发生
控制数据库语句
强制数据完整性

禁止更改的触发器

CREATE TRIGGER PreventProductChanges
ON Products
INSTEAD OF UPDATE
AS
    RAISERROR ('Updates of products are not permitted.
                Contact the database administrator if a change is needed.', 16, 1);

禁止并通知的触发器

CREATE TRIGGER PreventCustomersRemoval
ON Customers
INSTEAD OF DELETE
AS
    DECLARE @EmailBodyText NVARCHAR(50) =
                       (SELECT 'User "' + ORIGINAL_LOGIN() +
                        '" tried to remove a customer from the database.');

    RAISERROR ('Customer entries are not subject to removal.', 16, 1);

    EXECUTE SendNotification @RecipientEmail = '[email protected]'
                              ,@EmailSubject = 'Suspicious database behavior'
                              ,@EmailBody = @EmailBodyText;

含条件逻辑的触发器

CREATE TRIGGER ConfirmStock
ON Orders
INSTEAD OF INSERT
AS
    IF EXISTS (SELECT * FROM Products AS p
               INNER JOIN inserted AS i ON i.Product = p.Product
               WHERE p.Quantity < i.Quantity)
        RAISERROR ('You cannot place orders when there is no product stock.', 16, 1);
    ELSE
        INSERT INTO dbo.Orders (Customer, Product, Quantity, OrderDate, TotalAmount)
        SELECT Customer, Product, Quantity, OrderDate, TotalAmount FROM inserted;

含条件逻辑的触发器

触发器的条件逻辑

让我们来练习！

在 SQL Server 中构建与优化触发器