Simple trigger for auditing

Question

Post reply

Simple trigger for auditing

kuopaz

Default port

Points: 1449
More actions
January 20, 2022 at 3:29 pm

#3975282

I'm after a simple trigger to audit inserts and deletes on a single table.
If the PK does not exist in the audit table (i.e. an INSERT has occurred) then insert into the audit table.
If the PK exists in the audit table (i.e. an UPDATE has occurred) then insert into the audit table the new values if any of the non-key values have changed.
The table and the audit table will contain the same columns (plus any additional columns required in the audit table).
I'm struggling to find an example.
Also, are the data changes made by a trigger included in the transaction, so that they are rolled back if the INSERT or UPDATE on the table are rolled back - I think that is the case?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27060 More actions · Answer 1

I did a google search for "Auditing SQL Server with Triggers"

https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/

https://www.sqlshack.com/creating-smart-trigger-based-audit-trail-sql-server/

And, searching SQL Server Central for "Audit Triggers", I found

https://www.sqlservercentral.com/scripts/script-generates-audit-triggers

https://www.mssqltips.com/sqlservertip/1468/designing-tables-for-audit-data-in-sql-server/

https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/pop-rivetts-sql-server-faq-no-5-pop-on-the-audit-trail/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kuopaz Default port Points: 1449 More actions · Answer 2

Thanks. The first one is simple and a good starting point for me.

Why in the below is it necessary to join back to tblOrders?

create trigger tblTriggerAuditRecord on tblOrders
after update, insert
as
begin
  insert into tblOrdersAudit 
  (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
  select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 
  from  tblOrders t 
  inner join inserted i on t.OrderID=i.OrderID 
end
go

ratbak Ten Centuries Points: 1380 More actions · Answer 3

It's not. Everything just inserted or updated is in the virtual "inserted" table, so the join adds no value.

Good observation.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 4

That join is not needed.

are the data changes made by a trigger included in the transaction, so that they are rolled back if the INSERT or UPDATE on the table are rolled back?

Yes, the trigger is part of the transaction and will be rolled back if the trans fails.

If the PK exists in the audit table (i.e. an UPDATE has occurred) then insert into the audit table the new values if any of the non-key values have changed.

Do you want to log columns only if the value changes? Or just log every column every time?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kuopaz Default port Points: 1449 More actions · Answer 5

Good, I couldn't understand why the join was there.

Scott - I would want to log all columns, but only if one or more of the values have changed. An UPDATE that happened to not change any column values would not go into the audit.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 6

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER tblTriggerAuditRecord
ON dbo.tblOrders
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
INSERT INTO dbo.tblOrdersAudit 
  ( OrderID, OrderApprovalDateTime, OrderStatus, /*...,*/ UpdatedBy, UpdatedOn )
SELECT i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, /*...,*/ SUSER_SNAME(), GETDATE() 
FROM  dbo.tblOrders t 
INNER JOIN deleted d ON d.OrderID=t.OrderID 
WHERE
    (d.OrderApprovalDateTime IS NULL OR t.OrderApprovalDateTime <> d.OrderApprovalDateTime) OR
    (t.OrderStatus <> d.OrderStatus) /*OR ...*/
/*end of trigger*/
GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kuopaz Default port Points: 1449 More actions · Answer 7

Thanks.

FROM dbo.tblOrders t

INNER JOIN deleted d ON d.OrderID=t.OrderID

I was thinking it was:

FROM dbo.tblOrders t

LEFT JOIN deleted d ON d.OrderID=t.OrderID

or

FROM inserted i

LEFT JOIN deleted d ON i.OrderID=d.OrderID

ScottPletcher SSC Guru Points: 101117 More actions · Answer 8

Oops, quite true, since I did a combined trigger.

I suggest separating the INSERT and UPDATE triggers. Then you can use INNER JOIN and not have to test for INSERT vs UPDATE in the trigger.

...

AFTER UPDATE

...

For the INSERT trigger, you don't need to join to anything, since all the columns are known to be "changed", i.e. new.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".