Compartir a través de

Crear desencadenadores anidados

Los triggers DML y DDL se ejecutan de manera anidada cuando uno realiza una acción que activa otro trigger. Estas acciones pueden iniciar otros desencadenadores, etc. Los desencadenadores DML y DDL se pueden anidar hasta 32 niveles. Puede controlar si los desencadenadores AFTER se pueden anidar a través de la opción de configuración del servidor desencadenadores anidados . Los desencadenadores INSTEAD OF (solo los desencadenadores DML pueden ser desencadenadores INSTEAD OF) pueden anidarse independientemente de esta configuración.

Nota:

Cualquier referencia al código administrado de un desencadenador de Transact-SQL cuenta como un nivel con respecto al límite de anidamiento de 32 niveles. Los métodos invocados desde el código administrado no cuentan con este límite.

Si se permiten desencadenadores anidados y un desencadenador de la cadena inicia un bucle infinito, se excede el nivel de anidamiento y finaliza el desencadenador.

Puede usar desencadenadores anidados para realizar funciones útiles de mantenimiento, como guardar una copia de seguridad de las filas que fueron afectadas por un desencadenador anterior. Por ejemplo, puede crear un desencadenador en PurchaseOrderDetail que guarde una copia de seguridad de las filas de PurchaseOrderDetail que el desencadenador delcascadetrig eliminó. Con el delcascadetrig desencadenador en vigor, eliminar PurchaseOrderID 1965 de PurchaseOrderHeader elimina la fila o filas correspondientes de PurchaseOrderDetail. Para guardar los datos, puede crear un desencadenador DELETE en PurchaseOrderDetail que guarde los datos eliminados en otra tabla creada por separado, del_save. Por ejemplo:

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

No se recomienda usar desencadenadores anidados en una secuencia que depende del orden. Use desencadenadores independientes para realizar modificaciones en cascada de datos.

Nota:

Dado que los desencadenadores se ejecutan dentro de una transacción, un error en cualquier nivel de un conjunto de desencadenadores anidados cancela toda la transacción y se revierten todas las modificaciones de datos. Incluya instrucciones PRINT en los desencadenadores para que pueda determinar dónde se ha producido el error.

Desencadenadores recursivos

Un disparador AFTER no se llama a sí mismo de forma recursiva a menos que se establezca la opción de la base de datos RECURSIVE_TRIGGERS.

Hay dos tipos de recursividad:

  • Recursividad directa

    Esta recursividad se produce cuando un disparador se activa y realiza una acción que hace que el mismo disparador se active nuevamente. Por ejemplo, una aplicación actualiza la tabla T3; esto hace que trig3 desencadene el desencadenador. Trig3 actualiza la tabla T3 de nuevo; esto hace que el desencadenador Trig3 se active de nuevo.

    La recursividad directa también puede producirse cuando se llama de nuevo al mismo desencadenador, pero después de llamar a un desencadenador de otro tipo (AFTER o INSTEAD OF). En otras palabras, la recursividad directa de un desencadenador INSTEAD OF puede producirse cuando se llama al mismo desencadenador INSTEAD OF por segunda vez, incluso si se llama a uno o varios desencadenadores AFTER en medio. Del mismo modo, la recursión directa de un trigger AFTER puede producirse cuando se llama al mismo trigger AFTER por segunda vez, incluso si se ha llamado a uno o varios triggers INSTEAD OF entre medio. Por ejemplo, una aplicación actualiza la tabla T4. Esta actualización hace que se active el desencadenador INSTEAD OF Trig4 . Trig4 actualiza la tabla T5. Esta actualización hace que se active el desencadenador AFTER Trig5 . Trig5 actualiza la tabla T4 y esta actualización hace que el desencadenador INSTEAD OF Trig4 se active de nuevo. Esta cadena de eventos se considera recursividad directa para Trig4.

  • Recursividad indirecta

    Esta recursión ocurre cuando un disparador se activa y realiza una acción que provoca que se dispare otro disparador del mismo tipo (AFTER o INSTEAD OF). Este segundo desencadenador realiza una acción que hace que el desencadenador original se active de nuevo. En otras palabras, la recursividad indirecta puede producirse cuando se llama a un disparador INSTEAD OF por segunda vez, pero solo después de que se haya llamado a otro disparador INSTEAD OF entre medias. Del mismo modo, la recursión indirecta puede ocurrir cuando un trigger AFTER se activa por segunda vez, pero no hasta que se activa otro trigger AFTER entre medias. Por ejemplo, una aplicación actualiza la tabla T1. Esta actualización hace que se active el desencadenador AFTER Trig1 . Trig1 actualiza la tabla T2 y esta actualización hace que se active el desencadenador AFTER Trig2 . Trig2 a su vez actualiza la tabla T1 que hace que el desencadenador AFTER Trig1 se active de nuevo.

Solo se impide la recursividad directa de los desencadenadores AFTER cuando la opción de base de datos RECURSIVE_TRIGGERS está establecida en OFF. Para deshabilitar la recursividad indirecta de los desencadenadores AFTER, establezca también la opción del servidor de desencadenadores anidados en 0.

Ejemplos

En el ejemplo siguiente se muestra el uso de desencadenadores recursivos para resolver una relación de autorreferencia (también conocida como cierre transitivo). Por ejemplo, la tabla emp_mgr define lo siguiente:

  • Un empleado (emp) de una empresa.

  • El gerente de cada empleado (mgr).

  • El número total de empleados del árbol organizativo que dependen de cada empleado (NoOfReports).

Se puede usar un desencadenador UPDATE recursivo para mantener la NoOfReports columna up-to-date a medida que se insertan nuevos registros de empleados. El desencadenador INSERT actualiza la NoOfReports columna del registro de administrador, que actualiza recursivamente la NoOfReports columna de otros registros en la jerarquía de administración.

USE AdventureWorks2012;  
GO  
-- Turn recursive triggers ON in the database.  
ALTER DATABASE AdventureWorks2012  
   SET RECURSIVE_TRIGGERS ON;  
GO  
CREATE TABLE dbo.emp_mgr (  
   emp char(30) PRIMARY KEY,  
    mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),  
    NoOfReports int DEFAULT 0  
);  
GO  
CREATE TRIGGER dbo.emp_mgrins ON dbo.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 dbo.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 dbo.emp_mgrupd ON dbo.emp_mgr FOR UPDATE  
AS  
IF UPDATE (mgr)  
BEGIN  
   UPDATE dbo.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 dbo.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 dbo.emp_mgr(emp, mgr) VALUES  
    ('Harry', NULL)  
    ,('Alice', 'Harry')  
    ,('Paul', 'Alice')  
    ,('Joe', 'Alice')  
    ,('Dave', 'Joe');  
GO  
SELECT emp,mgr,NoOfReports  
FROM dbo.emp_mgr;  
GO  
-- Change Dave's manager from Joe to Harry  
UPDATE dbo.emp_mgr SET mgr = 'Harry'  
WHERE emp = 'Dave';  
GO  
SELECT emp,mgr,NoOfReports FROM emp_mgr;  
  
GO

Estos son los resultados antes de la actualización.

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

Estos son los resultados después de la actualización.

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

Para establecer la opción de desencadenadores anidados

Para establecer la opción de base de datos de RECURSIVE_TRIGGERS

Véase también

CREATE TRIGGER (Transact-SQL)
Configurar la opción de configuración del servidor de desencadenadores anidados