Usar desencadenadores anidados

Actualizado: 5 de diciembre de 2005

Los desencadenadores DML y DDL están anidados cuando un desencadenador realiza una acción que inicia otro desencadenador. Estas acciones pueden iniciar otros desencadenadores y así sucesivamente. Los desencadenadores DML y DDL se pueden anidar hasta un máximo de 32 niveles. Puede controlar si los desencadenadores AFTER se pueden anidar en la opción de configuración del servidor nested triggers. Los desencadenadores INSTEAD OF (sólo los desencadenadores DML pueden ser desencadenadores INSTEAD OF) se pueden anidar independientemente de esta configuración.

[!NOTA] Una referencia a código administrado desde un desencadenador Transact-SQL se considera como un nivel en lo que respecta al límite de anidamiento de 32 niveles. Los métodos a los que se llama desde el código administrado no cuentan para este límite.

Si se admiten desencadenadores anidados y un desencadenador de la cadena inicia un bucle infinito, se superará el nivel de anidamiento y se terminará el desencadenador.

Puede utilizar desencadenadores anidados para realizar funciones de mantenimiento, tales como almacenar una copia de seguridad de las filas que han sido 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 haya eliminado el desencadenador delcascadetrig. Con el desencadenador delcascadetrig activado, la eliminación del valor PurchaseOrderID 1965 de PurchaseOrderHeader elimina las filas correspondientes de PurchaseOrderDetail. Para guardar los datos, puede crear un desencadenador DELETE en PurchaseOrderDetail que guarde los datos eliminados en una nueva tabla, del_save. Por ejemplo:

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

No se recomienda utilizar desencadenadores anidados en una secuencia que dependa del orden. Utilice desencadenadores diferentes para realizar modificaciones de datos en cascada.

[!NOTA] Dado que los desencadenadores se ejecutan dentro de una transacción, un error en cualquier nivel de un conjunto de desencadenadores anidados anula toda la transacción y provoca que se reviertan todas las modificaciones de datos. Incluya instrucciones PRINT en los desencadenadores para poder determinar dónde se produjo el error.

Desencadenadores recursivos

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

Hay dos tipos de recursividad:

• Recursividad directa
  Esta recursividad se produce cuando un desencadenador se activa y realiza una acción que provoca que el mismo desencadenador se vuelva a activar. Por ejemplo, una aplicación actualiza la tabla T3 y esto provoca la activación del desencadenador Trig3. Trig3 vuelve a actualizar la tabla T3, lo que provoca una nueva activación del mismo desencadenador Trig3.
  En SQL Server 2005, también se puede producir la repetición directa cuando el mismo desencadenador se llama de nuevo, pero después de que se llame a un tipo diferente (AFTER o INSTEAD OF). Es decir, la repetición directa de un desencadenador INSTEAD OF puede producirse cuando se llama al mismo desencadenador INSTEAD OF por segunda vez, incluso cuando se llaman a uno o varios desencadenadores AFTER en medio. Del mismo modo, la repetición directa de un desencadenador AFTER puede producirse cuando se llama al mismo desencadenador AFTER por segunda vez, incluso cuando se llaman a uno o varios desencadenadores INSTEAD OF en 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 se active de nuevo el desencadenador INSTEAD OF Trig4. Esta cadena de eventos se considera una repetición directa de Trig4.
• Repetición indirecta
  Esta repetición se produce cuando se activa un desencadenador y realiza una acción que provoca la activación de otro desencadenador del mismo tipo (AFTER o INSTEAD OF). Este segundo desencadenador realiza una acción que provoca una nueva activación del desencadenador original. Es decir, la repetición indirecta se puede producir cuando se llama a un desencadenador INSTEAD OF por segunda vez, pero no hasta que se llama a otro desencadenador INSTEAD OF en medio. Del mismo modo, la repetición indirecta se puede producir cuando se llama a un desencadenador AFTER por segunda vez, pero no hasta que se llama a otro desencadenador AFTER en medio. 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. A su vez, Trig2 actualiza la tabla T1, lo que provoca que se vuelva a activar el desencadenador AFTER Trig1.

La repetición directa de los desencadenadores AFTER sólo se impide si la opción RECURSIVE_TRIGGERS de la base de datos se establece en OFF. Para deshabilitar la repetición indirecta de los desencadenadores AFTER, también debe establecer la opción nested triggers del servidor en 0.

Ejemplos

En el ejemplo siguiente se muestra la utilización de desencadenadores recursivos para solucionar una relación con referencia a sí misma (también denominada cierre transitivo). Por ejemplo, la tabla emp_mgr define los elementos siguientes:

• Un empleado (emp) de una empresa.
• El director de cada empleado (mgr).
• El número total de empleados en la estructura de la organización que dependen de cada empleado (NoOfReports).

Un desencadenador UPDATE recursivo puede servir para mantener actualizada la columna NoOfReports a medida que se insertan nuevos registros de empleado. El desencadenador INSERT actualiza la columna NoOfReports del registro de directores, que actualiza de modo recursivo la columna NoOfReports de otros registros superiores de la jerarquía de administración.

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

Resultados antes de la actualización.

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

Resultados tras 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 nested triggers

• sp_configure (Transact-SQL)

Para establecer la opción de base de datos RECURSIVE_TRIGGERS

• ALTER DATABASE (Transact-SQL)

Vea también

Conceptos

nested triggers (opción)
Ejecución de desencadenadores DML
Configurar las opciones de la base de datos

Otros recursos

CREATE TRIGGER (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios