Trigger for insert, update, delete

Question

I want to insert rows into the audit table whenever an insert, update or delete takes place in the master table "Table1" - doesn't matter which column is changed/inserted. I also want to add I, U or D on insert, update or delete. For insert and delete I am checking if rows exist in the inserted and deleted table. What is the best way to approach update.

My code for insert and delete is :

CREATE TRIGGER [dbo].[tr_Table1_InsertUpdate_Table1History_Insert]
ON [dbo].[Table1]
FOR INSERT, DELETE, UPDATE

AS
BEGIN
 IF EXISTS(SELECT * FROM Inserted)
 BEGIN
  INSERT INTO Table1History(...., ModificationType)
  SELECT ..., 'I'
  FROM Inserted
 END


 IF EXISTS(SELECT * FROM Deleted)
 BEGIN
  INSERT INTO Table1History(..., ModificationType)
  SELECT ..., 'D'
  FROM Deleted
 END

END
GO

Kindly help!

Solution 1

For updates, the original values for the row will be added to the deleted table, and the new values for the row will be added to the inserted table. So, to identify inserts, deletes and updates you would do the following

• Inserts - get the rows from inserted that are not in deleted
• Deletes - get the rows from deleted that are not in inserted.
• Updates - get the rows that are in both inserted and deleted

Solution 2

Below is an example of a trigger generated by ApexSQL Audit

It’s not a cheap tool but you can probably use it in trial mode to get the job done.

Notice the INSERT INTO dbo.AUDIT_LOG_DATA part and repeat it for every column you want to audit.

There are two tables in the background for storing the data and several stored procedures as well but this will get you going in the right direction.

CREATE TRIGGER [dbo].[tr_d_AUDIT_TableName]
ON [dbo].[TableName]
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
DECLARE 
    @IDENTITY_SAVE              varchar(50),
    @AUDIT_LOG_TRANSACTION_ID       Int,
    @PRIM_KEY               nvarchar(4000),
    --@TABLE_NAME               nvarchar(4000),
    @ROWS_COUNT             int

SET NOCOUNT ON


Select @ROWS_COUNT=count(*) from deleted
Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
    TABLE_NAME,
    TABLE_SCHEMA,
    AUDIT_ACTION_ID,
    HOST_NAME,
    APP_NAME,
    MODIFIED_BY,
    MODIFIED_DATE,
    AFFECTED_ROWS,
    [DATABASE]
)
values(
    'TableName',
    'dbo',
    3,  --  ACTION ID For DELETE
    CASE 
      WHEN LEN(HOST_NAME()) < 1 THEN ' '
      ELSE HOST_NAME()
    END,
    CASE 
      WHEN LEN(APP_NAME()) < 1 THEN ' '
      ELSE APP_NAME()
    END,
    SUSER_SNAME(),
    GETDATE(),
    @ROWS_COUNT,
    'DatabaseName'
)


Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()

INSERT
INTO dbo.AUDIT_LOG_DATA
(
    AUDIT_LOG_TRANSACTION_ID,
    PRIMARY_KEY_DATA,
    COL_NAME,
    OLD_VALUE_LONG,
    DATA_TYPE
    , KEY1
)
SELECT
    @AUDIT_LOG_TRANSACTION_ID,
    convert(nvarchar(1500), IsNull('[Order_ID]='+CONVERT(nvarchar(4000), OLD.[Order_ID], 0), '[Order_ID] Is Null')),
    'Order_ID',
    CONVERT(nvarchar(4000), OLD.[Order_ID], 0),
    'A'
    ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[Order_ID], 0))
FROM deleted OLD
WHERE
    OLD.[Order_ID] Is Not Null
END

Solution 3

Have you consider using AutoAudit?

AutoAudit is a SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with:

• Created, CreatedBy, Modified, ModifiedBy, and RowVersion (incrementing INT) columns to table
• Insert event logged to Audit table
• Updates old and new values logged to Audit table
• Delete logs all final values to the Audit tbale
• view to reconstruct deleted rows
• UDF to reconstruct Row History
• Schema Audit Trigger to track schema changes
• Re-code-gens triggers when Alter Table changes the table

Comments

[Tech Xie]

Thank you! Will try AutoAudit too.