Trigger in SQL Server - Get the type of Transaction done for Audit Table

Question

I am writing trigger for inserting records into my Audit table.

Whenever there is a change in data in my target table, the trigger will update the old value, new value to the audit table

Additionally there are columns called Transaction and Transaction_Status

Transaction column defines the type of transaction. Can be INSERT, UPDATE or DELETE.
Transaction_Status column indicates SUCCESS or FAILURE

How to achieve this ?

My trigger :

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

Once you fix your trigger to cover all three operations,

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

Another alternative is three separate triggers, one for each action.

Be wary of MERGE though if you are using it... Or be prepared for it when you move to SQL Server 2008 or beyond.

EDIT

I think what you may be after is an INSTEAD OF trigger instead (how ironic). Here is one example. Let's consider a very simple table with a PK column and a unique column:

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

And a simple log table to catch activity:

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

The following INSTEAD OF trigger will intercept INSERT/UPDATE/DELETE commands, attempt to replicate the work they would have done, and log whether it was a failure or success:

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

Let's try some very simple, implicit-transaction statements:

-- 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

Check the log:

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

Results:

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

Of course you probably want other columns on the log table, such as user, date/time, maybe even the original statement. This wasn't meant to be a fully comprehensive auditing solution, just an example.

As Mikael points out, this relies on the fact that the outer batch is a single command that starts an implicit transaction. The behavior will have to be tested if the outer batch is an explicit, multi-statement transaction.

Also note that this does not capture "failure" in the case where, say, an UPDATE affects zero rows. So you need to explicitly define what "failure" means - in some cases you may need to build your own failure handling in the outer code, not in a trigger.