Utilizzo di trigger nidificati

Entrambi i trigger DML e DDL vengono nidificati quando un trigger esegue un'operazione che ne avvia un altro. Tali operazioni possono quindi avviare altri trigger e così via. I trigger DML e DDL possono essere nidificati fino a un massimo di 32 livelli. Per gestire la nidificazione dei trigger AFTER, utilizzare l'opzione di configurazione del server nested triggers. I trigger INSTEAD OF (solo DML) possono essere nidificati indipendentemente da questa impostazione.

Nota

Qualsiasi riferimento a codice gestito da un trigger Transact-SQL viene conteggiato come un unico livello rispetto al limite dei 32 livelli di nidificazione. I metodi richiamati da codice gestito non vengono inclusi nel conteggio per questo limite.

Se è consentito l'utilizzo di trigger nidificati e un trigger della catena avvia un ciclo infinito, il livello di nidificazione viene superato e il trigger viene interrotto.

È possibile utilizzare i trigger nidificati per eseguire funzioni di manutenzione, utili quali l'archiviazione di una copia di backup delle righe interessate da un trigger precedente. È ad esempio possibile creare un trigger su PurchaseOrderDetail per salvare una copia di backup delle righe di PurchaseOrderDetail eliminate dal trigger delcascadetrig. Se il trigger delcascadetrig è attivo, l'eliminazione di PurchaseOrderID 1965 dalla tabella PurchaseOrderHeader implica l'eliminazione della riga o delle righe corrispondenti da PurchaseOrderDetail. Per salvare i dati eliminati in un'altra tabella creata separatamente denominata del_save, creare un trigger DELETE su PurchaseOrderDetail. Ad esempio:

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

È consigliabile non utilizzare trigger nidificati in una sequenza dipendente dall'ordinamento. Utilizzare trigger distinti per eseguire modifiche a catena dei dati.

Nota

Poiché i trigger vengono eseguiti all'interno di una transazione, un errore a qualsiasi livello di un set di trigger nidificati annulla l'intera transazione con conseguente rollback di tutte le modifiche apportate ai dati. Per determinare la posizione in cui si è verificato l'errore, includere nei trigger le istruzioni PRINT.

Trigger ricorsivi

Un trigger AFTER non chiama se stesso in modo ricorsivo a meno che non sia stata impostata l'opzione di database RECURSIVE_TRIGGERS.

Esistono due tipi di ricorsione:

  • Ricorsione diretta

    Questo tipo di ricorsione si verifica quando un trigger viene attivato ed esegue un'azione che attiva nuovamente lo stesso trigger. Ad esempio, un'applicazione aggiorna la tabella T3 che attiva il trigger Trig3. Trig3 aggiorna nuovamente la tabella T3, che attiva nuovamente il trigger Trig3.

    In SQL Server 2008 la ricorsione diretta può inoltre verificarsi quando lo stesso trigger viene chiamato nuovamente, ma solo dopo la chiamata di un trigger di tipo diverso (AFTER o INSTEAD OF). In altri termini, la ricorsione diretta di un trigger INSTEAD OF può verificarsi quando lo stesso trigger INSTEAD OF viene chiamato per la seconda volta, anche se nel frattempo sono stati chiamati uno o più trigger AFTER. Analogamente, la ricorsione diretta di un trigger AFTER può verificarsi quando lo stesso trigger AFTER viene chiamato per la seconda volta, anche se nel frattempo sono stati chiamati uno o più trigger INSTEAD OF. Ad esempio, un'applicazione aggiorna la tabella T4. L'aggiornamento attiva il trigger INSTEAD OF Trig4. Trig4 aggiorna la tabella T5. Questo aggiornamento attiva il trigger AFTER Trig5. Trig5 aggiorna la tabella T4 e questa operazione attiva nuovamente il trigger INSTEAD OF Trig4. Questa catena di eventi costituisce una ricorsione diretta per il trigger Trig4.

  • Ricorsione indiretta

    Questo tipo di ricorsione si verifica quando un trigger viene attivato ed esegue un'azione che attiva un altro trigger dello stesso tipo (AFTER o INSTEAD OF). Il secondo esegue un'operazione che implica nuovamente l'attivazione del trigger originale. In altri termini, la ricorsione indiretta può verificarsi quando un trigger INSTEAD OF viene chiamato per la seconda volta, ma non prima della chiamata di un altro trigger INSTEAD OF. Analogamente, la ricorsione indiretta può verificarsi quando un trigger AFTER viene chiamato per la seconda volta, ma non prima della chiamata di un altro trigger AFTER. Ad esempio, un'applicazione aggiorna la tabella T1. L'aggiornamento attiva il trigger AFTER Trig1. Trig1 aggiorna la tabella T2 e questa operazione attiva il trigger AFTER Trig2. Trig2 aggiorna, a sua volta, la tabella T1 che attiva nuovamente il trigger AFTER Trig1.

Se l'opzione di database RECURSIVE_TRIGGERS è impostata su OFF, viene evitata solo la ricorsione diretta dei trigger AFTER. Per disabilitare la ricorsione indiretta dei trigger AFTER, impostare su 0 anche l'opzione del server nested triggers.

Esempi

Nell'esempio seguente viene illustrato l'utilizzo dei trigger ricorsivi per risolvere una relazione autoreferenziale, nota anche come chiusura transitiva. Ad esempio, nella tabella emp_mgr vengono definiti gli elementi seguenti:

  • Il dipendente (emp) di una società.

  • Il responsabile di ciascun dipendente (mgr).

  • Il numero totale di dipendenti nell'albero organizzativo di cui ogni dipendente è responsabile (NoOfReports).

È possibile utilizzare un trigger UPDATE ricorsivo per aggiornare la colonna NoOfReports quando vengono inseriti i record di nuovi dipendenti. Il trigger INSERT aggiorna la colonna NoOfReports del record del responsabile che aggiorna in modo ricorsivo la colonna NoOfReports degli altri record fino ai livelli più alti dell'organigramma.

USE AdventureWorks2008R2;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks2008R2
   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

Risultati prima dell'aggiornamento.

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

Risultati dopo l'aggiornamento.

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

Per impostare l'opzione nested triggers

Per impostare l'opzione di database RECURSIVE_TRIGGERS