trigger - Disparador en SQL Server: obtenga el tipo de transacción realizada para la tabla de auditoría

trigger sql server

Question

Estoy escribiendo el disparador para insertar registros en mi tabla de Audit .

Siempre que haya un cambio en los datos en mi tabla objetivo, el activador actualizará el valor anterior, el nuevo valor en la tabla de auditoría

Además, hay columnas llamadas Transaction y Transaction_Status

Transaction columna de Transaction define el tipo de transacción. Puede ser INSERT , UPDATE o DELETE .
Transaction_Status columna Transaction_Status indica SUCCESS o FAILURE

¿Cómo lograr esto?

Mi disparador:

Alter Trigger TR_test
ON subscribers
FOR UPDATE
AS BEGIN
DECLARE @OldValue xml,@NewValue xml, @changedby varchar(50), @ReferenceId int
-----------------------------------------------------------------------------
SELECT @OldValue=b.username, @NewValue=a.username, 
       @ReferenceId = a.user_id, @changedby = a.modified_by
FROM inserted a, deleted b;
----------------------------------------------------------------------------- 
INSERT INTO [dbo].[audit_log]
           ([old_value],[new_value],[module],[reference_id],[transaction]
           ,[transaction_status],[stack_trace],[modified_on],[modified_by])
     VALUES
(@OldValue,@NewValue,''Subscriber'',@ReferenceId,''_transaction'',
''_transaction_status'',''_stack_trace'',getdate(),555)

-----------------------------------------------------------------------------
END

Answer 1

Una vez que arregle su disparador para cubrir las tres operaciones,

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
  IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    SET @action = ''UPDATE'';
  END
  ELSE
  BEGIN
    SET @action = ''INSERT'';
  END
ELSE
BEGIN
  SET @action = ''DELETE'';
END

Otra alternativa es tres factores desencadenantes por separado, uno para cada acción.

Sin embargo, desconfíe de MERGE si lo está usando ... O esté preparado para ello cuando se traslade a SQL Server 2008 o más adelante.

EDITAR

Creo que lo que puedes estar buscando es un disparador INSTEAD OF (qué irónico). Aquí hay un ejemplo. Consideremos una tabla muy simple con una columna PK y una columna única:

CREATE TABLE dbo.foobar(id INT PRIMARY KEY, x CHAR(1) UNIQUE);
GO

Y una tabla de registro simple para captar actividad:

CREATE TABLE dbo.myLog
(
    foobar_id INT, 
    oldValue  XML, 
    newValue  XML, 
    [action]  CHAR(6), 
    success   BIT
);
GO

El siguiente activador INSTEAD OF interceptará los comandos INSERT/UPDATE/DELETE , intentará replicar el trabajo que habría realizado y registrará si fue un fracaso o un éxito:

CREATE TRIGGER dbo.foobar_inst
ON dbo.foobar
INSTEAD OF INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @action  CHAR(6), @success BIT;

  SELECT @action  = ''DELETE'', @success = 1;

  IF EXISTS (SELECT 1 FROM inserted)
  BEGIN
    IF EXISTS (SELECT 1 FROM deleted)
      SET @action = ''UPDATE'';
    ELSE
      SET @action = ''INSERT'';
  END

  BEGIN TRY
    IF @action = ''INSERT''
      INSERT dbo.foobar(id, x) SELECT id, x FROM inserted;

    IF @action = ''UPDATE''
      UPDATE f SET x = i.x FROM dbo.foobar AS f
        INNER JOIN inserted AS i ON f.id = i.id;

    IF @action = ''DELETE''
        DELETE f FROM dbo.foobar AS f
          INNER JOIN inserted AS i ON f.id = i.id;
  END TRY
  BEGIN CATCH
    ROLLBACK; -- key part here!

    SET @success = 0;
  END CATCH

  IF @action = ''INSERT''
    INSERT dbo.myLog SELECT i.id, NULL, 
      (SELECT * FROM inserted WHERE id = i.id FOR XML PATH),
      @action, @success FROM inserted AS i;

  IF @action = ''UPDATE''
    INSERT dbo.myLog SELECT i.id, 
      (SELECT * FROM deleted  WHERE id = i.id FOR XML PATH),
      (SELECT * FROM inserted WHERE id = i.id FOR XML PATH),
      @action, @success FROM inserted AS i;

  IF @action = ''DELETE''
    INSERT dbo.myLog SELECT d.id, 
      (SELECT * FROM deleted  WHERE id = d.id FOR XML PATH),
      NULL, @action, @success FROM deleted AS d;
END
GO

Probemos algunas declaraciones de transacciones implícitas muy simples:

-- these succeed:

INSERT dbo.foobar SELECT 1, ''x'';
GO
INSERT dbo.foobar SELECT 2, ''y'';
GO

-- fails with PK violation:

INSERT dbo.foobar SELECT 1, ''z'';
GO

-- fails with UQ violation:

UPDATE dbo.foobar SET x = ''y'' WHERE id = 1;
GO

Verifique el registro:

SELECT foobar_id, oldValue, newValue, action, success FROM dbo.myLog;

Resultados:

foobar_id oldValue                      newValue                      action success
--------- ----------------------------- ----------------------------- ------ -------
1         NULL                          <row><id>1</id><x>x</x></row> INSERT 1
2         NULL                          <row><id>2</id><x>y</x></row> INSERT 1
1         NULL                          <row><id>1</id><x>z</x></row> INSERT 0
1         <row><id>1</id><x>x</x></row> <row><id>1</id><x>y</x></row> UPDATE 0

Por supuesto, es probable que desee otras columnas en la tabla de registro, como usuario, fecha / hora, incluso la declaración original. Esto no pretendía ser una solución de auditoría completa, solo un ejemplo.

Como señala Mikael, esto se basa en el hecho de que el lote externo es un solo comando que inicia una transacción implícita. El comportamiento tendrá que probarse si el lote externo es una transacción explícita de varias instrucciones.

También tenga en cuenta que esto no captura "falla" en el caso en que, por ejemplo, una ACTUALIZACIÓN afecta a cero filas. Por lo tanto, debe definir explícitamente lo que significa "falla", en algunos casos puede necesitar construir su propia gestión de fallas en el código externo, no en un disparador.