Como os gatilhos DML são usados

Criando e Otimizando Triggers no SQL Server

Florin Angelescu

Instructor

Por que usar gatilhos DML?

Iniciar ações ao manipular dados
Impedir manipulação de dados
Rastrear mudanças em dados ou objetos do banco
Auditoria de usuários e segurança do banco

Decidindo entre AFTER e INSTEAD OF

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- Disparado após
-- o evento de disparo (UPDATE)
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- Disparado em vez de
-- o evento de disparo (UPDATE)
INSTEAD OF UPDATE
AS
{trigger_actions_section};

Decidindo entre AFTER e INSTEAD OF

Gatilho AFTER

O evento inicial dispara o gatilho

O evento inicial é executado

As ações do gatilho são executadas

Como funciona um gatilho AFTER

Gatilho INSTEAD OF

O evento inicial dispara o gatilho

O evento inicial não é mais executado

As ações do gatilho são executadas

Como funciona um gatilho INSTEAD OF

Exemplo de uso de gatilho AFTER

Dados são inseridos em uma tabela de vendas
Iniciar um procedimento de limpeza de dados
Gerar um relatório com os resultados do procedimento
Notificar o administrador do banco

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

Exemplo de uso de gatilho INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 45W   | 0     |
| Lume  | Ultra    | 50W   | 0     |

A potência muda para alguns modelos

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

Exemplo de uso de gatilho INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |

A potência muda para alguns modelos
Atualizar somente os produtos sem estoque

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END

Exemplo de uso de gatilho INSTEAD OF

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |
| Ecco  | Standard | 35W   | 100   |

A potência muda para alguns modelos
Atualizar somente os produtos sem estoque
Adicionar novas linhas para produtos com estoque

-- A primeira parte foi truncada por motivos de espaço
IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END
ELSE
    INSERT INTO Bulbs
    SELECT * FROM inserted;

Perguntas de prática

Criando e Otimizando Triggers no SQL Server