Создание триггеров

Триггеры — это специальные хранимые процедуры, которые автоматически выполняются при возникновении определенных событий в базе данных. Вы определяете триггеры для поддержания целостности данных, принудительного применения бизнес-правил и автоматизации операций базы данных, не требуя кода на уровне приложения.

Общие сведения об основах триггера

Триггер реагирует на изменения данных или изменения схемы в базе данных. При создании триггера необходимо указать событие, активируемое и выполняющее действия.

Триггеры выполняются автоматически. В отличие от хранимых процедур, которые вы вызываете явно, триггеры срабатывают в ответ на инструкции INSERT, UPDATE, DELETE или DDL. Это автоматическое выполнение делает их эффективными для применения правил, которые должны применяться последовательно во всех изменениях данных.

SQL Server поддерживает две основные категории триггеров: триггеры DML (язык обработки данных) и триггеры DDL (язык определения данных). Триггеры DML реагируют на изменения данных таблицы, а триггеры DDL реагируют на изменения схемы, такие как CREATE, ALTERили DROP операторы.

Создание триггеров DML для изменений данных

Триггеры DML отслеживают и реагируют на изменения данных в таблицах или представлениях. Вы определяете их как триггеры AFTER или INSTEAD OF триггеры.

Триггеры AFTER выполняются после завершения выполняющей инструкции. База данных сначала выполняет изменение данных, а затем запускает код триггера. Триггеры AFTER используются для проверки изменений, обновления связанных таблиц или изменений журналов:

CREATE TRIGGER tr_UpdateInventory
ON Sales.OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE Inventory.Products
    SET QuantityInStock = QuantityInStock - i.Quantity
    FROM Inventory.Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END;

Триггеры INSTEAD OF заменяют исходную инструкцию изменения данных. Код триггера выполняется вместо операции INSERT, UPDATE или DELETE. Триггеры INSTEAD OF используются для изменения представлений, которые в обычных условиях не принимают прямые изменения, или для реализации сложной бизнес-логики.

CREATE TRIGGER tr_UpdateOrderView
ON Sales.OrderSummaryView
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Sales.Orders
    SET OrderStatus = i.OrderStatus,
        ModifiedDate = GETDATE()
    FROM Sales.Orders o
    INNER JOIN inserted i ON o.OrderID = i.OrderID;
END;

С помощью триггеров DML вы обращаетесь к вставленным и удаленным псевдо-таблицам. Эти временные таблицы хранят копии затронутых строк. INSERTоперации заполняют таблицу inserted, DELETE операции заполняют таблицу deleted, а UPDATE операции заполняют обе таблицы: старыми значениями в deleted и новыми значениями в inserted.

Реализация триггеров для определенных событий

Вы указываете, какие события изменения данных активируют триггер. Один триггер может реагировать на несколько событий, сочетая INSERTи UPDATEDELETE в определении триггера.

Для точного управления создаются отдельные триггеры для каждой операции. Этот подход упрощает код и упрощает обслуживание триггеров:

CREATE TRIGGER tr_LogPriceChanges
ON Products.Catalog
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Price)
    BEGIN
        INSERT INTO Audit.PriceHistory (ProductID, OldPrice, NewPrice, ChangeDate)
        SELECT d.ProductID, d.Price, i.Price, GETDATE()
        FROM deleted d
        INNER JOIN inserted i ON d.ProductID = i.ProductID
        WHERE d.Price <> i.Price;
    END;
END;

В то же время можно объединить события, если одна и та же логика применяется к нескольким операциям. Например, можно создать один триггер аудита, который отвечает на INSERT, UPDATEи DELETE:

CREATE TRIGGER tr_AuditEmployeeChanges
ON HR.Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Operation NVARCHAR(10);
    
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
        SET @Operation = 'INSERT';
    ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @Operation = 'UPDATE';
    ELSE
        SET @Operation = 'DELETE';
    
    INSERT INTO Audit.EmployeeLog (EmployeeID, Operation, ChangeDate)
    SELECT COALESCE(i.EmployeeID, d.EmployeeID), @Operation, GETDATE()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;

Функция UPDATE() помогает определить, какие столбцы были изменены. Вы проверяете определенные столбцы, чтобы избежать ненужной обработки, если для бизнес-логики имеют значение только определенные поля.

Применение лучших практик триггеров

Триггеры влияют на производительность базы данных, так как они выполняются с каждой соответствующей операцией. Вы записываете эффективный код триггера, чтобы свести к минимуму влияние на пропускную способность транзакций.

Держите логику триггера сосредоточенной и минимальной. Выполнение только важных операций в теле триггера. Для сложных или трудоемких операций рассмотрите возможность ведения журнала сведений о событии и асинхронной обработке их с помощью отдельного задания:

CREATE TRIGGER tr_QueueLargeOrders
ON Sales.Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO Processing.OrderQueue (OrderID, TotalAmount, QueuedDate)
    SELECT OrderID, TotalAmount, GETDATE()
    FROM inserted
    WHERE TotalAmount > 10000;
END;

Избегайте рекурсивных операций, когда изменение триггера приводит к повторному срабатыванию того же триггера. RECURSIVE_TRIGGERS Задайте параметр базы данных соответствующим образом и настройте триггеры, чтобы предотвратить бесконечные циклы.

Правильно обрабатывать ошибки в триггерах. Поведение транзакций зависит от обработки ошибок. Если триггер обнаруживает ошибку и вы её не обрабатываете, SQL Server откатывает как триггер, так и исходную инструкцию.

CREATE TRIGGER tr_ValidateOrderDate
ON Sales.Orders
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE OrderDate > GETDATE())
    BEGIN
        THROW 50001, 'Order date cannot be in the future', 1;
    END;
END;

Тщательно задокументируйте триггеры. Другие разработчики должны понять, почему триггеры существуют и что они делают, так как они выполняются невидимо во время обычных операций базы данных.

Теперь, когда вы понимаете, как создавать и реализовывать триггеры, вы можете изучить, как они интегрируются с другими объектами программирования для создания комплексных решений базы данных.