sql - trazabilidad - set identity_insert

¿Cómo evito que una activación de base de datos vuelva a aparecer?

Question

Tengo el siguiente desencadenante en una tabla para una base de datos de SQL Server 2008. Está recurriendo, así que tengo que detenerlo.

Después de insertar o actualizar un registro, intento simplemente actualizar un solo campo en esa tabla.

Aquí está el disparador:

ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] 
   ON  [dbo].[tblMedia]
   BEFORE INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @IdMedia INTEGER,
        @NewSubject NVARCHAR(200)   

    SELECT @IdMedia = IdMedia, @NewSubject = Title
    FROM INSERTED

    -- Now update the unique subject field.
    -- NOTE: dbo.CreateUniqueSubject is my own function. 
    --       It just does some string manipulation.
    UPDATE tblMedia
    SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) + 
                      CAST((IdMedia) AS VARCHAR(10))
    WHERE tblMedia.IdMedia = @IdMedia
END

¿Alguien puede decirme cómo puedo evitar que el inserto del disparador vuelva a disparar otro gatillo?

Answer 1

Creo que lo tengo :)

Cuando el título se "actualiza" (léase: insertado o actualizado), actualice el tema único. Cuando el desencadenante se ejecuta por segunda vez, el campo de tema único se actualiza, por lo que se detiene y deja el desencadenador.

Además, lo he hecho manejar MÚLTIPLES filas que se cambian -> Siempre me olvido de esto con los factores desencadenantes.

ALTER TRIGGER [dbo].[tblMediaAfterInsert] 
   ON  [dbo].[tblMedia]
   FOR INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    -- If the Title is getting inserted OR updated then update the unique subject.
    IF UPDATE(Title) BEGIN
        -- Now update all the unique subject fields that have been inserted or updated.
        UPDATE tblMedia 
        SET UniqueTitle = dbo.CreateUniqueSubject(b.Title) + 
                          CAST((b.IdMedia) AS VARCHAR(10))
        FROM tblMedia a
            INNER JOIN INSERTED b on a.IdMedia = b.IdMedia
    END
END

Answer 2

No estoy seguro de si ya es pertinente para la pregunta del OP, pero en caso de que haya venido aquí para averiguar cómo evitar que la recurrencia o recursión mutua ocurra en un desencadenante, puede probar esto de la siguiente manera:

IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/

link MSDN

Answer 3

Para completar, añadiré algunas cosas. Si tiene un activador posterior particular que solo desea ejecutar una vez, puede configurarlo para que se ejecute último utilizando sp_settriggerorder.

También consideraría si no fuera mejor combinar los disparadores que están haciendo la recursión en un disparador.

Answer 4

Puede tener una columna NULLABLE separada que indique si se configuró UniqueTitle.

Establezca el valor verdadero en un desencadenador y haga que el desencadenador no haga nada si su valor es verdadero en "INSERTADO"

Answer 5

Veo tres posibilidades:

1. Desactivar la recursión de activador

   Esto evitará que un disparador disparado llame a otro activador o se llame de nuevo. Para hacer esto, ejecuta este comando:

   ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF
   GO

2. Use un gatillo EN LUGAR DE ACTUALIZAR, INSERTAR

   Usando un disparador INSTEAD OF puede controlar cualquier columna que se actualice / inserte, e incluso reemplazar antes de llamar al comando.

3. Controle el gatillo evitando usar IF IF UPDATE

   La prueba de la columna le indicará con una precisión razonable si el activador se está llamando a sí mismo. Para hacer esto use la cláusula IF UPDATE() como:

   ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate]
      ON  [dbo].[tblMedia]
      FOR INSERT, UPDATE
   AS
   BEGIN
       SET NOCOUNT ON
       DECLARE @IdMedia INTEGER,
           @NewSubject NVARCHAR(200)   
   
       IF UPDATE(UniqueTitle)
         RETURN;
   
       -- What is the new subject being inserted?
       SELECT @IdMedia = IdMedia, @NewSubject = Title
       FROM INSERTED
   
       -- Now update the unique subject field.
       -- NOTE: dbo.CreateUniqueSubject is my own function. 
       --       It just does some string manipulation.
       UPDATE tblMedia
       SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) + 
                         CAST((IdMedia) AS VARCHAR(10))
       WHERE tblMedia.IdMedia = @IdMedia
   END

Answer 6

TRIGGER_NESTLEVEL se puede usar para evitar la recursión de un disparador específico, pero es importante pasar la identificación del objeto del disparador a la función. De lo contrario, también evitará que el disparador se dispare cuando otro desencadenador realice una inserción o actualización:

   IF TRIGGER_NESTLEVEL(OBJECT_ID(''dbo.mytrigger'')) > 1
         BEGIN
             PRINT ''mytrigger exiting because TRIGGER_NESTLEVEL > 1 '';
             RETURN;
     END;

Desde MSDN :

Cuando no se especifican parámetros, TRIGGER_NESTLEVEL devuelve la cantidad total de activadores en la pila de llamadas. Esto incluye a sí mismo.

Referencia: evitar desencadenantes recursivos

Answer 7

ALTER DATABASE <dbname> SET RECURSIVE_TRIGGERS OFF

RECURSIVE_TRIGGERS {ON | APAGADO }

ON Se permite la activación recursiva de los desencadenadores AFTER.

OFF Solo se permite el disparo recursivo directo de los desencadenadores AFTER. Para deshabilitar también la recursión indirecta de los desencadenadores DESPUÉS, configure la opción del servidor de desencadenadores anidados en 0 utilizando sp_configure.

Solo se evita la recursión directa cuando RECURSIVE_TRIGGERS está desactivado. Para deshabilitar la recursión indirecta, también debe establecer la opción del servidor de triggers anidados en 0.

El estado de esta opción puede determinarse examinando la columna is_recursive_triggers_on en la vista del catálogo sys.databases o la propiedad IsRecursiveTriggersEnabled de la función DATABASEPROPERTYEX.