Tracking changes of tables

Other SQL Server Topics
1

Hello,

I need to create audit tables for tracking changes on insert/update/delete for each table of my database. (what fields have changed, username that made the change, modification date, action[insert/update/delete)

What is the best way of doing this?

Until now (in previous version of SQL Server) I was using a trigger in each table that was sending the "old" record to another table, but I'm not sure if that is the best way of tracking changes.

Thank you in advance.

2

you can look into DB audit for that in SQL 2012, you should be able to get the info.

3

First of all, thank you for your answer.

I'm using the express version, so db auditing as given from SQL Server in not an option from what I have read. Am I wrong?

4

Hi Chriz,
Given you were using express edition, your choice will be limited, if i'm not wrong, trigger might be the only option that you have given the version limitation.
If you do upgrade to Enterprise edition, you can have DB audit, CDC which might can fit your needs.

5

Agreed, in Express, trigger might be the only choice. But be sure the trigger is very efficient, as otherwise it will slow down your processing a lot.

1 Like
6

Thank you both for the answers, I was kind of hoping that there was another solution for express as well! :blush:

Any ideas for efficient triggers?

This is the one I use so far:

CREATE TRIGGER [dbo].[Area_Audit]
 ON [dbo].[tblAREA]
 FOR Update,Delete AS
 
 BEGIN
DECLARE @auditType varchar(10)
 	-- Audit OLD record.
 	IF EXISTS (Select i.IDENTITY from Inserted i)
 	-- Record is updated
 	BEGIN
 		SET @auditType = 'Updated'
 	END
 	ELSE
 	-- Record is deleted
 	BEGIN
 		SET @auditType = 'Deleted'
 	END
 
 	INSERT INTO AUD_AREA
 	(LogType,
 	AREA_CODE )
 SELECT @auditType,
 	d.AREA_CODE,
  FROM deleted d
 END
7

That's reasonably efficient, but it doesn't identify which column(s) changed, as specified in the original post.

CREATE TRIGGER [dbo].[Area_Audit]
 ON [dbo].[tblAREA]
 AFTER Delete,Update 
AS
 
SET NOCOUNT ON;
DECLARE @auditType char(6);

IF EXISTS (Select 1 from Deleted)
    SET @auditType = 'Update'
ELSE
    SET @auditType = 'Delete'

INSERT INTO AUD_AREA
( LogType, User, Modification_Date, AREA_CODE )
SELECT
  @auditType, ORIGINAL_LOGIN(), GETDATE(), d.AREA_CODE
FROM deleted d

GO --end of trigger
8

Thank you for the solution!