Partilhar via

  • Artigo

Usando gatilhos aninhados

Os gatilhos DML e DDL são aninhados quando um gatilho executa uma ação que inicia outro gatilho. Essas ações podem iniciar outros gatilhos, e assim por diante. Os gatilhos DML e DDL podem ser aninhados em até 32 níveis. Qualquer referência a um código gerenciado de um gatilho Transact-SQL conta como um nível em relação ao limite de 32 níveis de aninhamento. Os métodos invocados do código gerenciado não contam em relação a esse limite.

Você pode controlar se os gatilhos AFTER podem ser aninhados pela opção de configuração do servidor nested triggers. Os gatilhos INSTEAD OF (apenas gatilhos DML podem ser gatilhos INSTEAD OF) podem ser aninhados, independentemente dessa configuração.

ObservaçãoObservação

No SQL Server 2000, nenhum gatilho AFTER aninhado dentro de um gatilho INSTEAD OF é disparado quando a opção de configuração do servidor nested triggers está desativada. No SQL Server 2005 ou em versões posteriores, o primeiro gatilho AFTER aninhado dentro de um gatilho INSTEAD OF dispara mesmo que a opção de configuração do servidor nested triggers esteja definida como 0. Porém, nessa configuração, os gatilhos AFTER posteriores não são disparados. É recomendável que você revise seus aplicativos para verificar se há gatilhos aninhados. Verifique se os aplicativos ainda estão de acordo com as regras de negócio em relação a esse comportamento quando a opção de configuração do servidor nested triggers está definida como 0 e faça as modificações apropriadas.

Se gatilhos aninhados forem permitidos e um gatilho na cadeia iniciar um loop infinito, o nível de aninhamento será excedido e o gatilho será encerrado.

Você pode usar gatilhos aninhados para executar funções de manutenção úteis, como armazenar uma cópia de backup de linhas afetadas por um gatilho anterior. Por exemplo, você pode criar um gatilho em PurchaseOrderDetail que salva uma cópia de backup das linhas de PurchaseOrderDetail que o gatilho delcascadetrig excluiu. Com o gatilho delcascadetrig em vigor, excluir PurchaseOrderID 1965 de PurchaseOrderHeader exclui a linha correspondente de PurchaseOrderDetail. Para salvar os dados, você pode criar um gatilho DELETE em PurchaseOrderDetail que salva os dados excluídos em outra tabela criada separadamente, del_save. Por exemplo:

CREATE TRIGGER savedel
   ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
   INSERT del_save
   SELECT * FROM deleted

Nós não recomendamos usar gatilhos aninhados em uma seqüência dependente de ordem. Use gatilhos separados para colocar modificações de dados em cascata.

ObservaçãoObservação

Como os gatilhos são executados em uma transação, uma falha em qualquer nível de um conjunto de gatilhos aninhados cancela toda a transação e todas as modificações de dados são revertidas. Inclua instruções PRINT em seus gatilhos de modo que você possa determinar onde a falha ocorreu.

Gatilhos recursivos

Um gatilho AFTER não pode ser chamado recursivamente a menos que a opção do banco de dados RECURSIVE_TRIGGERS seja definida.

Existem dois tipos de grupos de recursão:

  • Recursão direta

    Essa recursão ocorre quando um gatilho é acionado e executa uma ação que faz com que o mesmo gatilho seja acionado novamente. Por exemplo, um aplicativo atualiza a tabela T3; isso faz com que o gatilho Trig3 seja acionado. Trig3 atualiza a tabela T3 novamente; isso faz com que o gatilho Trig3 seja acionado novamente.

    Em SQL Server 2008, a recursão direta também pode ocorrer quando o mesmo gatilho é chamado novamente, mas após um gatilho de um tipo diferente (AFTER ou INSTEAD OF) ter sido chamado. Em outras palavras, a recursão direta de um gatilho INSTEAD OF pode ocorrer quando o mesmo gatilho INSTEAD OF é chamado pela segunda vez, mesmo se um ou mais gatilhos AFTER forem chamados no meio disso. Da mesma maneira, a recursão direta de um gatilho AFTER pode ocorrer quando o mesmo gatilho AFTER é chamado pela segunda vez, mesmo se um ou mais gatilhos INSTEAD OF forem chamados no meio disso. Por exemplo, um aplicativo atualiza a tabela T4. Essa atualização faz com que o gatilho INSTEAD OF Trig4 seja acionado. Trig4 atualiza a tabela T5. Essa atualização faz com que o gatilho AFTER Trig5 seja acionado. Trig5 atualiza a tabela T4 e essa atualização faz com que o gatilho INSTEAD OF Trig4 seja acionado novamente. Essa cadeia de eventos é considerada uma recursão direta para Trig4.

  • Recursão indireta

    Essa recursão ocorre quando um gatilho é acionado e executa uma ação que faz com que outro gatilho do mesmo tipo (AFTER ou INSTEAD OF) seja acionado. Esse segundo gatilho executa uma ação que faz com que o gatilho original seja acionado novamente. Em outras palavras, a recursão indireta pode ocorrer quando um gatilho INSTEAD OF for chamado pela segunda vez, mas não até que outro gatilho INSTEAD OF seja chamado no meio disso. Da mesma maneira, a recursão indireta pode ocorrer quando um gatilho AFTER for chamado pela segunda vez, mas não até que outro gatilho AFTER seja chamado no meio disso. Por exemplo, um aplicativo atualiza a tabela T1. Essa atualização faz com que o gatilho AFTER Trig1 seja acionado. Trig1 atualiza a tabela T2 e essa atualização faz com que o gatilho AFTER Trig2 seja acionado. Trig2, por sua vez, atualiza a tabela T1, o que faz com que o gatilho AFTER Trig1 seja acionado novamente.

Somente a recursão direta de gatilhos AFTER é impedida quando a opção do banco de dados RECURSIVE_TRIGGERS estiver definida como OFF. Para desabilitar a recursão indireta de gatilhos AFTER, defina também a opção do servidor nested triggers como 0.

Exemplos

O exemplo a seguir mostra o uso de gatilhos recursivos para resolver uma relação de auto-referência (também conhecida como fechamento transitivo). Por exemplo, a tabela emp_mgr define o seguinte:

  • Um funcionário (emp) em uma empresa.

  • O gerente de cada funcionário (mgr).

  • O número total de funcionários na árvore organizacional reportando-se a cada funcionário (NoOfReports).

Um gatilho recursivo UPDATE pode ser usado para manter a coluna NoOfReports atualizada à medida que novos registros de funcionário forem sendo inseridos. O gatilho INSERT atualiza a coluna NoOfReports do registro do gerente, o que atualiza recursivamente a coluna NoOfReports de outros registros acima da hierarquia de gerenciamento.

USE AdventureWorks;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks
   SET RECURSIVE_TRIGGERS ON;
GO
CREATE TABLE emp_mgr (
   emp char(30) PRIMARY KEY,
    mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
    NoOfReports int DEFAULT 0
);
GO
CREATE TRIGGER emp_mgrins ON emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30)
DECLARE c1 CURSOR FOR
   SELECT emp_mgr.emp
   FROM   emp_mgr, inserted
   WHERE emp_mgr.emp = inserted.mgr

OPEN c1
FETCH NEXT FROM c1 INTO @e
WHILE @@fetch_status = 0
BEGIN
   UPDATE emp_mgr
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
   WHERE emp_mgr.emp = @e                            -- added employee.

   FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
--   1. Only singleton updates on emp_mgr.
--   2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
   UPDATE emp_mgr
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
   FROM inserted                            -- (no. of reports) by
   WHERE emp_mgr.emp = inserted.mgr         -- 1 for the new report.

   UPDATE emp_mgr
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
   FROM deleted                             -- (no. of reports) by 1
   WHERE emp_mgr.emp = deleted.mgr          -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave';
GO
SELECT * FROM emp_mgr;
GO

Eis os resultados antes da atualização.

emp                            mgr                           NoOfReports
------------------------------ ----------------------------- -----------
Alice                          Harry                          2
Dave                           Joe                            0
Harry                          NULL                           1
Joe                            Alice                          1
Paul                           Alice                          0

Eis os resultados após a atualização.

emp                            mgr                           NoOfReports
------------------------------ ----------------------------- -----------
Alice                          Harry                          2
Dave                           Harry                          0
Harry                          NULL                           2
Joe                            Alice                          0
Paul                           Alice                          0

Para definir a opção de gatilhos aninhados

Para definir a opção do banco de dados RECURSIVE_TRIGGERS