Creating a Trigger to be used to Audit a table after Insert, Update or Delete

Transact-SQL
1

So for starters I guess there is no Trigger for Before Insert, Update, Delete

OK So I have created a Trigger will say on my main table each time a column has been Update, inserted or deleted. This trigger is being used to create an Audit trail of all data that is changed.

I know it is very simple looking but it seems to do most of what I want. Except if a DB user who has write capability and is using T-SQL through SQL Manager or another tool, how could I capture who they are logged in as.

Also any other suggestions as to creating an Audit table

Alter Trigger TestTrigger ON TableTestTriggers
After Insert, Update, Delete
as

Begin
--Set noaccount on

Declare 
@AuditDate Datetime,
@TableTestTriggersID as int,
@v_Row1 as nvarchar(50),
@v_Row2 as nvarchar(50),
@v_Row3 as nvarchar(50),
@UserName as nvarchar(50),
@EnterDate as datetime


Set @AuditDate = Getdate()
Set @TableTestTriggersID = (Select ID from inserted)
Set @v_Row1 = (Select Row1 from inserted)
Set @v_Row2 = (Select Row2 from inserted)
Set @v_Row3 = (Select Row3 from inserted)
Set @UserName = (Select Name from inserted)
Set @EnterDate = (Select EnterDate from inserted)


Insert into TableTestTriggers_Audit (TableTestTriggersID, Row1, Row2, Row3,Name, EnterDate, AuditDate)
Values (@TableTestTriggersID, @v_Row1, @v_Row2, @v_Row3, @UserName,@EnterDate, @AuditDate )

End

2

Read SQL Server 2008 change data capture vs triggers in audit trail - Stack Overflow and Best way to implement an audit trail in SQL Server? - Stack Overflow to get an idea of your options.

If you decide to go for the trigger solution, make sure INSERTs, UPDATEs and DELETEs of multiple records in one transaction get logged too. The trigger you provided will only keep track of one change, no matter how many recods were inserted. You need to replace it by something like:

INSERT INTO TableTestTriggers_Audit (TableTestTriggersID, Row1, Row2, Row3
      , Name, EnterDate, AuditDate)
SELECT i.ID, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate() 
FROM inserted AS i

As your trigger gets fired after an INSERT or an UPDATE or a DELETE, your trigger must first determine what action activated it:

--find oud if the event that activated the trigger was an INSERT, an UPDATE, a DELETE or got activated for nothing (like by UPDATE table_name SET col1 = 'Hello' WHERE id < 0, when all ID's are positive integers).

DECLARE @Action as char(6);
SET @Action = CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
                       THEN 'UPDATE'
                    WHEN EXISTS(SELECT * FROM INSERTED)
                       THEN 'INSERT'
                    WHEN EXISTS(SELECT * FROM DELETED)
                       THEN 'DELETE'
                    ELSE 'NUTIN'
               END

followed by

if @action = 'INSERT'
BEGIN
    INSERT INTO TableTestTriggers_Audit_INSERTS (TableTestTriggersID, Row1, Row2, Row3
          , Name, EnterDate, AuditDate)
    SELECT i.ID, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate() 
    FROM inserted AS i
END
if @action = 'UPDATE'
BEGIN
    INSERT INTO TableTestTriggers_Audit_UPDATES (TableTestTriggersID, Row1, Row2, Row3
          , Name, EnterDate, AuditDate)
    SELECT i.ID, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate() 
    FROM inserted AS i
END
if @action = 'DELETE'
BEGIN
    INSERT INTO TableTestTriggers_Audit_DELETES (TableTestTriggersID, Row1, Row2, Row3
          , Name, EnterDate, AuditDate)
    SELECT D.ID, D.Row1, D.Row2, D.Row3, D.Name, D.EnterDate, GetDate() 
    FROM DELETED as D
END

In case you want to go for one audit table:

if @action IN ('INSERT', 'UPDATE')
BEGIN
    INSERT INTO TableTestTriggers_Audit (TableTestTriggersID, Action, Row1, Row2, Row3
          , Name, EnterDate, AuditDate)
    SELECT i.ID, @action, i.Row1, i.Row2, i.Row3, i.Name, i.EnterDate, GetDate() 
    FROM inserted AS i
END
if @action = 'DELETE'
BEGIN
    INSERT INTO TableTestTriggers_Audit (TableTestTriggersID, Action, Row1, Row2, Row3
          , Name, EnterDate, AuditDate)
    SELECT D.ID, @action, D.Row1, D.Row2, D.Row3, D.Name, D.EnterDate, GetDate() 
    FROM DELETED as D
END

how could I capture who they are logged in as.

Best is to let the application capure that and write it to the table, so you can use
that in the audit table too.

3

Don't audit INSERTs. It's a totally unnecessary duplication of data that will instantly double the size of your table with all the same effect on backup, restores, index maintenance, memory usage, etc.

If you have SQ Server 2016 or greater and the table isn't crazy wide, use Temporal Tables. It's something that MS did really well.

If you have something less than 2016, then just audit Updates and Deletes. If you insert a row, it will be present in the original table. If you update that row, the update will be in the original table and the original row will be in the audit table.

If you used SCD Type 2 or 6 (6 is SCD 2+4, which works out very well), you'll need your trigger to end-date the rows being audited.

4

I just found out about Temporal Tables this morning, so we are now looking into this. Even our cloud admin didn't know about them.

5

Thank you very much everyone