Crear desencadenadores anidadosCreate Nested Triggers

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL DatabaseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

Los desencadenadores DML y DDL están anidados cuando un desencadenador realiza una acción que inicia otro desencadenador.Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. Estas acciones pueden iniciar otros desencadenadores y así sucesivamente.These actions can initiate other triggers, and so on. Los desencadenadores DML y DDL se pueden anidar hasta un máximo de 32 niveles.DML and DDL triggers can be nested up to 32 levels. Puede controlar si los desencadenadores AFTER se pueden anidar en la opción de configuración del servidor nested triggers .You can control whether AFTER triggers can be nested through the nested triggers server configuration option. Los desencadenadores INSTEAD OF (solo los desencadenadores DML pueden ser desencadenadores INSTEAD OF) se pueden anidar independientemente de esta configuración.INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

Nota

Una referencia a código administrado desde un desencadenador Transact-SQLTransact-SQL se considera como un nivel en lo que respecta al límite de anidamiento de 32 niveles.Any reference to managed code from a Transact-SQLTransact-SQL trigger counts as one level against the 32-level nesting limit. Los métodos invocados desde el código administrado no cuentan para este límite.Methods invoked from within managed code do not count against this limit.

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.If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates.

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.You can use nested triggers to perform useful housekeeping functions such as storing a backup copy of rows affected by a previous trigger. 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 .For example, you can create a trigger on PurchaseOrderDetail that saves a backup copy of the PurchaseOrderDetail rows that the delcascadetrig trigger deleted. Con el desencadenador delcascadetrig activado, la eliminación del valor PurchaseOrderID 1965 de PurchaseOrderHeader elimina las filas correspondientes de PurchaseOrderDetail.With the delcascadetrig trigger in effect, deleting PurchaseOrderID 1965 from PurchaseOrderHeader deletes the corresponding row or rows from PurchaseOrderDetail. Para guardar los datos, puede crear un desencadenador DELETE en PurchaseOrderDetail que guarde los datos eliminados en una nueva tabla, del_save.To save the data, you can create a DELETE trigger on PurchaseOrderDetail that saves the deleted data into another separately created table, del_save. Por ejemplo:For example:

CREATE TRIGGER Purchasing.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.We do not recommend using nested triggers in an order-dependent sequence. Utilice desencadenadores diferentes para realizar modificaciones de datos en cascada.Use separate triggers to cascade data modifications.

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.Because triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. Incluya instrucciones PRINT en los desencadenadores para poder determinar dónde se produjo el error.Include PRINT statements in your triggers so that you can determine where the failure has occurred.

Desencadenadores recursivosRecursive Triggers

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.An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.

Hay dos tipos de recursividad:There are two types of recursion:

  • Recursión directaDirect recursion

    Esta recursividad se produce cuando un desencadenador se activa y realiza una acción que provoca que el mismo desencadenador se vuelva a activar.This recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. Por ejemplo, una aplicación actualiza la tabla T3 y esto provoca la activación del desencadenador Trig3 .For example, an application updates table T3; this causes trigger Trig3 to fire. Trig3 vuelve a actualizar la tabla T3 , lo que provoca una nueva activación del mismo desencadenador Trig3 .Trig3 updates table T3 again; this causes trigger Trig3 to fire again.

    También se puede producir la repetición directa cuando se llama de nuevo al mismo desencadenador, pero después de que se llame a un desencadenador de un tipo diferente (AFTER o INSTEAD OF).Direct recursion can also occur when the same trigger is called again, but after a trigger of a different type (AFTER or INSTEAD OF) is called. 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.In other words, direct recursion of an INSTEAD OF trigger can occur when the same INSTEAD OF trigger is called for a second time, even if one or more AFTER triggers are called in between. 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.Likewise, direct recursion of an AFTER trigger can occur when the same AFTER trigger is called for a second time, even if one or more INSTEAD OF triggers are called in between. Por ejemplo, una aplicación actualiza la tabla T4.For example, an application updates table T4. Esta actualización hace que se active el desencadenador INSTEAD OF Trig4 .This update causes INSTEAD OF trigger Trig4 to fire. Trig4 actualiza la tabla T5.Trig4 updates table T5. Esta actualización hace que se active el desencadenador AFTER Trig5 .This update causes AFTER trigger Trig5 to fire. Trig5 actualiza la tabla T4 y esta actualización hace que se active de nuevo el desencadenador INSTEAD OF Trig4 .Trig5 updates table T4, and this update causes INSTEAD OF trigger Trig4 to fire again. Esta cadena de eventos se considera una repetición directa de Trig4.This chain of events is considered direct recursion for Trig4.

  • Recursión indirectaIndirect recursion

    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).This recursion occurs when a trigger fires and performs an action that causes another trigger of the same type (AFTER or INSTEAD OF) to fire. Este segundo desencadenador realiza una acción que provoca una nueva activación del desencadenador original.This second trigger performs an action that causes the original trigger to fire again. 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.In other words, indirect recursion can occur when an INSTEAD OF trigger is called for a second time, but not until another INSTEAD OF trigger is called in between. 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.Likewise, indirect recursion can occur when an AFTER trigger is called for a second time, but not until another AFTER trigger is called in between. Por ejemplo, una aplicación actualiza la tabla T1.For example, an application updates table T1. Esta actualización hace que se active el desencadenador AFTER Trig1 .This update causes AFTER trigger Trig1 to fire. Trig1 actualiza la tabla T2 y esta actualización hace que se active el desencadenador AFTER Trig2 .Trig1 updates table T2, and this update causes AFTER trigger Trig2 to fire. A su vez,Trig2 actualiza la tabla T1 , lo que provoca que se vuelva a activar el desencadenador AFTER Trig1 .Trig2 in turn updates table T1 that causes AFTER trigger Trig1 to fire again.

La repetición directa de los desencadenadores AFTER solo se impide si la opción RECURSIVE_TRIGGERS de la base de datos se establece en OFF.Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. Para deshabilitar la repetición indirecta de los desencadenadores AFTER, también debe establecer la opción nested triggers del servidor en 0.To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.

EjemplosExamples

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 clausura transitiva).The following example shows using recursive triggers to solve a self-referencing relationship (also known as transitive closure). Por ejemplo, la tabla emp_mgr define los elementos siguientes:For example, the table emp_mgr defines the following:

  • Un empleado (emp) de una empresa.An employee (emp) in a company.

  • El director de cada empleado (mgr).The manager for each employee (mgr).

  • El número total de empleados en la estructura de la organización que dependen de cada empleado (NoOfReports).The total number of employees in the organizational tree reporting to each employee (NoOfReports).

Un desencadenador UPDATE recursivo puede servir para mantener actualizada la columna NoOfReports a medida que se insertan nuevos registros de empleado.A recursive UPDATE trigger can be used to keep the NoOfReports column up-to-date as new employee records are inserted. 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.The INSERT trigger updates the NoOfReports column of the manager record, which recursively updates the NoOfReports column of other records up the management hierarchy.

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  

Resultados antes de la actualización.Here are the results before the update.

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

Resultados tras la actualización.Here are the results after the update.

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

Para establecer la opción nested triggersTo set the nested triggers option

Para establecer la opción de base de datos RECURSIVE_TRIGGERSTo set the RECURSIVE_TRIGGERS database option

Consulte tambiénSee Also

CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
Establecer la opción de configuración del servidor Desencadenadores anidadosConfigure the nested triggers Server Configuration Option