Criar gatilhos

Concluído

Os gatilhos são procedimentos especiais armazenados que são executados automaticamente quando eventos específicos ocorrem em seu banco de dados. Você define gatilhos para manter a integridade de dados, impor regras de negócios e automatizar operações de banco de dados sem a necessidade de código no nível do aplicativo.

Entenda os conceitos básicos do gatilho

Um gatilho responde a alterações de esquema ou modificação de dados no banco de dados. Ao criar um gatilho, especifique o evento que o ativa e as ações que ele executa.

Os gatilhos são executados automaticamente. Ao contrário dos procedimentos armazenados que você chama explicitamente, os gatilhos disparam em resposta a INSERT, UPDATE, DELETE ou instruções DDL. Essa execução automática os torna poderosos para impor regras que devem ser aplicadas consistentemente em todas as modificações de dados.

O SQL Server dá suporte a duas categorias principais de gatilhos: gatilhos DML (Linguagem de Manipulação de Dados) e gatilhos DDL (Linguagem de Definição de Dados ). Os gatilhos DML respondem a alterações nos dados da tabela, enquanto os gatilhos DDL respondem a alterações de esquema como instruções CREATE, ALTER, ou DROP.

Criar gatilhos DML para modificações de dados

Os gatilhos DML monitoram e respondem a alterações de dados em tabelas ou exibições. Você os define como gatilhos AFTER ou gatilhos INSTEAD OF.

Os gatilhos AFTER são executados após a conclusão da instrução que os desencadeou. O banco de dados primeiro executa a modificação de dados e, em seguida, executa o código de gatilho. ** Você pode usar gatilhos AFTER para validar alterações, atualizar tabelas relacionadas ou registrar modificações:

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;

Os gatilhos INSTEAD OF substituem a instrução de modificação de dados original. O código de gatilho é executado em vez das operações INSERT, UPDATE ou DELETE. Você usa gatilhos INSTEAD OF para modificar exibições que normalmente não aceitariam modificações diretas ou implementar uma lógica de negócios complexa:

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;

Com gatilhos DML, você acessa as pseudo-tabelas inserted e deleted. Essas tabelas temporárias armazenam cópias das linhas afetadas. INSERT as operações populam a tabela inserida , DELETE as operações preenchem a tabela excluída e UPDATE as operações preenchem ambas as tabelas com valores antigos em valores excluídos e novos inseridos.

Implementar gatilhos para eventos específicos

Especifique quais eventos de modificação de dados ativam o gatilho. Um único gatilho pode responder a vários eventos combinando INSERT, UPDATEe DELETE na definição de gatilho.

Para um controle preciso, você cria gatilhos separados para cada operação. Essa abordagem simplifica seu código e torna seus gatilhos mais fáceis de manter:

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;

Ao mesmo tempo, você pode combinar eventos quando a mesma lógica se aplica a várias operações. Por exemplo, você pode criar um único gatilho de auditoria que responde a INSERT, UPDATEe 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;

A UPDATE() função ajuda a determinar quais colunas foram alteradas. Você verifica colunas específicas para evitar o processamento desnecessário quando apenas determinados campos importam para sua lógica de negócios.

Aplique práticas recomendadas de gatilhos

Os gatilhos afetam o desempenho do banco de dados porque são executados a cada operação de qualificação. Você escreve um código de gatilho eficiente para minimizar o impacto na taxa de transferência da transação.

Mantenha sua lógica de gatilho focada e mínima. Execute apenas operações essenciais dentro do corpo do gatilho. Para operações complexas ou demoradas, considere registrar os detalhes do evento e processá-los de forma assíncrona por meio de um trabalho separado:

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;

Evite operações recursivas em que uma modificação de gatilho faça com que o mesmo gatilho seja acionado novamente. Configure a opção de banco de dados RECURSIVE_TRIGGERS adequadamente e projete seus gatilhos para evitar loops infinitos.

Trate os erros adequadamente dentro dos gatilhos. O comportamento da transação depende do tratamento de erros. Se um gatilho encontrar um erro e você não o tratar, o SQL Server reverterá tanto o gatilho quanto a instrução original:

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;

Documente seus gatilhos detalhadamente. Outros desenvolvedores precisam entender por que os gatilhos existem e o que eles fazem, pois são executados de forma invisivelmente durante operações normais de banco de dados.

Agora que você entende como criar e implementar gatilhos, está pronto para explorar como eles se integram a outros objetos de programação para criar soluções de banco de dados abrangentes.