Adding simple trigger-based auditing to your SQL Server database - Jon Galloway

Adding simple trigger-based auditing to your SQL Server database

How do you track changes to data in your database? There are a variety of supported auditing methods for SQL Server, including comprehensive C2 security auditing, but what do you do if you're solving a business rather than a security problem, and you're interested in tracking the following kinds of information:

  • What data has been updated recently
  • Which tables have not been updated recently
  • Who modified the price of Steeleye Stout to $20 / unit, and when did they do it?
  • What was the unit price for Steeleye Stout before Jon monkeyed with it?

There are a number of ways to design this into your solution from the start, for example:

  • The application is designed so that all changes are logged
  • All data changes go through a data access layer which logs all changes
  • The database is constructed in such a way that logging information is included in each table, perhaps set via a trigger

What if we're not starting from scratch?

But what do you do if you need to add lightweight auditing to an existing solution, in which data can be modified via a variety of direct access methods? When I ran into that challenge, I decided to use Nigel Rivett's SQL Server Auditing triggers. I read about some concern with the performance impact, but this database wasn't forecasted to have a high update rate. Nigel's script works by adding a trigger for INSERT, UPDATE, and DELETE on a single table. The trigger catches data changes, then saves out the information (such as table name, the primary key values, the column name that was altered, and the before and after values for that column) to an Audit table.

I needed to track every table in the database, though, and I expected the database schema to continue to change. I was able to generalize the solution a bit, because the database convention didn't use any no compound primary keys. I created the script listed below, which loops through all tables in the database with the exception of the Audit table, of course, since auditing changes to the audit table is both unnecessary and recursive. I'm also skipping sysdiagrams; you could include any other tables you don't want to track to that list as well.

The nice thing about the script I'm including below is that you can run it after making some schema changes and it will make sure that all newly added tables are included in the change tracking / audit, too.

Here's an example of what you'd see in the audit table for an Update followed by an Insert. Notice that the Update shows type U and a single column updated, while the Insert (type I) shows all columns added, one on each row:

Sample Audit Data

While this information is pretty unstructured, it's not difficult to run some useful reports. For instance, we can easily find things like

  • which tables were updated recently
  • which tables have not been updated in the past year
  • which tables have never been updated
  • all changes made by a specific user in a time period
  • most active tables in a time period

While it's not as easy, it's possible to backtrack from the current state to determine the state of a row in a table at a certain point in time. It's generally possible to dig out the state of an entire table at a point in time, but a change table isn't a good a fit for temporal data tracking - the right solution there is to start adding Modified By and Modified On columns to the required tables.

Note that we're only tracking data changes here. If you'd like to track schema changes, take a look at SQL Server 2005's DDL triggers.

Enough talking, give us the script!

Sure. I'll repeat that there are some disclaimers to the approach -  performance, it'll only track changes to tables with a primary key, etc. If you want to know more about the trigger itself, I'd recommend starting with Nigel's article. However, it worked great for our project.

Published Sunday, January 27, 2008 1:20 AM by Jon Galloway
Filed under: ,

Comments

# re: Adding simple trigger-based auditing to your SQL Server database

Just a foot note for anyone reading this in the future and thinking about implementing this: IMO this is one of those, "Look what we can do!" type of scripts that works theoretically but not in practice. I can think of no scenario in which anyone should implement such a trigger in the real world. Every time I see this OO-type of design posted, I have to always say that most SQL DBAs/developers disagree with this practice vehemently since there are automated tools that give you much more control over your auditing than a generic trigger (whether it be CLR or SQL-based).

Sunday, January 27, 2008 10:54 AM by Scott Whigham

# re: Adding simple trigger-based auditing to your SQL Server database

I've run into a problem using triggers. I need to log the user who deletes a record but the record is deleted before the column value for LastUser can be updated. I also have an update trigger so an update before the delete would generate an unwanted log entry.

Sunday, January 27, 2008 12:26 PM by rrobbins

# re: Adding simple trigger-based auditing to your SQL Server database

Or you could just but ApexSQL's great Audit product for a nominal price and call it a day.

Sunday, January 27, 2008 2:40 PM by John Mo

# re: Adding simple trigger-based auditing to your SQL Server database

Wow, dynamic, dynamic SQL. :-)

Sunday, January 27, 2008 4:16 PM by Duncan Smart

# re: Adding simple trigger-based auditing to your SQL Server database

@Scott Whigam - Appreciate your feedback. I used the above script in a real world business application, and it did work. It's not as flexible as an automated tool or a product, but some applications don't need a full-featured auditing solution - they just need change tracking information in a table for reporting purposes. In that scenario, the above solution worked very well.

Sunday, January 27, 2008 6:36 PM by Jon Galloway

# re: Adding simple trigger-based auditing to your SQL Server database

Just a potential gotcha with triggers. If you've got any SQL using @@IDENTITY, the inserts into the Audit can cause the value of @@IDENTITY to not reflect the value you are expecting. Make VERY sure that all you other SQL is using Scope_Identity() when you want the last identity value assigned (e.g. for inserts)

Sunday, January 27, 2008 7:47 PM by Marc Brooks

# re: Adding simple trigger-based auditing to your SQL Server database

Thanks a lot..... it worked.. performance is not a problem if u filter the tables based on needs .also u can filter updates and primary key of inserts and deletes.... its the simple way to track a small DB.

Thursday, February 7, 2008 6:45 AM by Binoy Bastin.

# re: Adding simple trigger-based auditing to your SQL Server database

I used this audit idea, but i have a question. i am working with an ASP NET app, that works with windows authentication. I want that the user saved in Audit table be the one that is accesing the application. Can this be possible in a domain context?.

Example:

                 Audit Table

AuditId                           UserName

1         .. ... ... ... ... ..   DOMAIN1\user1

2                                 DOMAIN1\user2  

Thursday, February 21, 2008 3:41 PM by Orlando

# re: Adding simple trigger-based auditing to your SQL Server database

Yup, use User_id() to get the spid and then suser_sname() to get the windows name.

Monday, March 3, 2008 4:43 AM by Cees

# re: Adding simple trigger-based auditing to your SQL Server database

Jon, This was a great start to what I was looking for.  However I wanted to create a unique audit table for each table.  I modified the code above to create a unique audit table. It may be a little sloppy but here it is if anyone is interest.

DECLARE

@Create_Audit_Table_SQL varchar(MAX),

@Create_Trigger_SQL varchar(MAX),

@TABLE_NAME sysname,

@Trigger_Select_Col_Current varchar(MAX),

@Create_Audit_Col_Old varchar(MAX),

@Trigger_Select_Col_Old varchar(MAX),

@COLUMN VARCHAR(MAX),

@COLUMN_NAME VARCHAR(50)

SET NOCOUNT ON

--Select all the tables in the database but the Audit tables and the sysdiagram table

SELECT @TABLE_NAME = MIN(TABLE_NAME)

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE= 'BASE TABLE'

AND (TABLE_NAME != 'sysdiagrams'

AND RIGHT(TABLE_NAME, 6) != '_AUDIT')

--Loop through the tables

WHILE @TABLE_NAME IS NOT NULL

BEGIN

-- Initialize/reset variables

SET @Create_Audit_Table_SQL = 'CREATE TABLE ' + @TABLE_NAME + '_AUDIT ('

SET @Create_Audit_Col_Old = ''

SET @Trigger_Select_Col_Old = ''

SET @Trigger_Select_Col_Current = ''

-- Create a cursor for looping through the columns in the table

DECLARE ColumnInfo_cursor CURSOR FOR

SELECT

CASE

-- Creating the formatted column line for a create table column

WHEN DATA_TYPE IN ('decimal', 'numeric') THEN CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' +  DATA_TYPE + '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR(10), NUMERIC_SCALE)+  '), ')

WHEN CHARACTER_MAXIMUM_LENGTH IS NULL OR DATA_TYPE = 'image' OR DATA_TYPE IN ('ntext', 'image', 'text', 'xml', 'sql_variant') THEN  CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' + DATA_TYPE + ', ')

WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' + DATA_TYPE + '(MAX), ')

ELSE CONVERT(VARCHAR(MAX), COLUMN_NAME + ' ' +  DATA_TYPE + '(' + CONVERT(VARCHAR(10), CHARACTER_MAXIMUM_LENGTH) + '), ')

END

, COLUMN_NAME

FROM

INFORMATION_SCHEMA.COLUMNS

Where

TABLE_NAME = @TABLE_NAME

ORDER BY ORDINAL_POSITION

OPEN ColumnInfo_cursor

FETCH NEXT FROM ColumnInfo_cursor

INTO @COLUMN, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Create_Audit_Table_SQL = @Create_Audit_Table_SQL + @COLUMN

SET @Create_Audit_Col_Old = @Create_Audit_Col_Old + 'old' + @COLUMN

SET @Trigger_Select_Col_Current = @Trigger_Select_Col_Current + @COLUMN_NAME + ', '

SET @Trigger_Select_Col_Old = @Trigger_Select_Col_Old + 'old' +  @COLUMN_NAME + ', '

FETCH NEXT FROM  ColumnInfo_cursor

INTO @COLUMN, @COLUMN_NAME

END

--Add to more rows to the table for a date and type stamp

SET @Create_Audit_Table_SQL = @Create_Audit_Table_SQL + @Create_Audit_Col_Old + 'AuditDate datetime DEFAULT (GetDate()), AuditType char(1))'

CLOSE ColumnInfo_cursor

DEALLOCATE ColumnInfo_cursor

-- Uncomment out the code line below if you want to drop your audit tables

-- WARNING: You will lose all history of the audit table if you do this

-- EXEC('IF OBJECT_ID (''' + @TABLE_NAME + '_AUDIT'') IS NOT NULL DROP TABLE ' + @TABLE_NAME + '_AUDIT')

-- Create the audit table if it doesn't exist.  Do not create audit tables for Audit tables

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= @TABLE_NAME + '_AUDIT') AND RIGHT(@TABLE_NAME, 6) != '_AUDIT'

BEGIN

-- PRINT @Create_Audit_Table_SQL

SELECT @Create_Audit_Table_SQL

EXEC(@Create_Audit_Table_SQL)

END

-- Create triggers for all tables except the audit tables

IF RIGHT(@TABLE_NAME, 6) != '_AUDIT'

BEGIN

-- Delete the trigger if it exist

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

-- Create the trigger

SET @Create_Trigger_SQL = 'create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete

as

declare @AuditType char(1),  @PKCols VARCHAR(MAX), @SQL VARCHAR(MAX)

--Find the Primary keys to be used in the inserted and deleted outer join

select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = ''' + @TABLE_NAME + '''

and CONSTRAINT_TYPE = ''PRIMARY KEY''

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select * into #ins from inserted

select * into #del from deleted

if exists (select * from inserted)

if exists (select * from deleted)

SET @AuditType = ''U''

else

SET @AuditType = ''I''

else

SET @AuditType = ''D''

EXEC(''INSERT INTO ' + @TABLE_NAME + '_AUDIT ('

-- Add the columns - current and old to the select

SET @Create_Trigger_SQL = @Create_Trigger_SQL + @Trigger_Select_Col_Current + @Trigger_Select_Col_Old + ' AuditType)

SELECT i.*, d.*, '''''' + @AuditType + '''''' FROM #ins i full outer join #del d '' + @PKCols )'

-- PRINT @Create_Trigger_SQL

SELECT @Create_Trigger_SQL

EXEC(@Create_Trigger_SQL)

END

-- Next table

SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME > @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

END

Wednesday, May 28, 2008 9:04 PM by Wayne Fontes

# re: Adding simple trigger-based auditing to your SQL Server database

When I execute the code

USE PUBS

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')

DROP TABLE Audit

CREATE TABLE Audit

(

AuditID [int]IDENTITY(1,1) NOT NULL,

Type char(1),

TableName varchar(128),

PrimaryKeyField varchar(1000),

PrimaryKeyValue varchar(1000),

FieldName varchar(128),

OldValue varchar(1000),

NewValue varchar(1000),

UpdateDate datetime DEFAULT (GetDate()),

UserName varchar(128)

)

GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname

SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE

TABLE_TYPE = 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

AND TABLE_NAME!= 'Audit'

WHILE @TABLE_NAME IS NOT NULL

BEGIN

EXEC('IF OBJECT_ID (''' + @TABLE_NAME + '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

SELECT @sql = 'create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete

as

declare @bit int ,

@field int ,

@maxfield int ,

@char int ,

@fieldname varchar(128) ,

@TableName varchar(128) ,

@PKCols varchar(1000) ,

@sql varchar(2000),

@UpdateDate varchar(21) ,

@UserName varchar(128) ,

@Type char(1) ,

@PKFieldSelect varchar(1000),

@PKValueSelect varchar(1000)

select @TableName = ''' + @TABLE_NAME+ '''

-- date and user

select @UserName = system_user ,

@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

-- Action

if exists (select * from inserted)

if exists (select * from deleted)

select @Type = ''U''

else

select @Type = ''I''

else

select @Type = ''D''

-- get list of columns

select * into #ins from inserted

select * into #del from deleted

-- Get primary key columns for full outer join

select@PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = @TableName

and CONSTRAINT_TYPE = ''PRIMARY KEY''

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert

select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = @TableName

and CONSTRAINT_TYPE = ''PRIMARY KEY''

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c  

where  pk.TABLE_NAME = @TableName  

and CONSTRAINT_TYPE = ''PRIMARY KEY''  

and c.TABLE_NAME = pk.TABLE_NAME  

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null

begin

raiserror(''no PK on table %s'', 16, -1, @TableName)

return

end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

while @field < @maxfield

begin

select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

select @bit = (@field - 1 )% 8 + 1

select @bit = power(2,@bit - 1)

select @char = ((@field - 1) / 8) + 1

if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')

begin

select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''

select @sql = @sql + '' select '''''' + @Type + ''''''''

select @sql = @sql + '','''''' + @TableName + ''''''''

select @sql = @sql + '','' + @PKFieldSelect

select @sql = @sql + '','' + @PKValueSelect

select @sql = @sql + '','''''' + @fieldname + ''''''''

select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''

select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''

select @sql = @sql + '','''''' + @UpdateDate + ''''''''

select @sql = @sql + '','''''' + @UserName + ''''''''

select @sql = @sql + '' from #ins i full outer join #del d''

select @sql = @sql + @PKCols

select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname

select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)''

select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)''

exec (@sql)

end

end

'

SELECT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME> @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

AND TABLE_NAME!= 'Audit'

END

I am getting these errors

Server: Msg 170, Level 15, State 1, Procedure authors_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure discounts_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure employee_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure jobs_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure pub_info_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure publishers_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure roysched_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure sales_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure stores_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure titleauthor_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

Server: Msg 170, Level 15, State 1, Procedure titles_ChangeTracking, Line 37

Line 37: Incorrect syntax near '='.

I could not get what is the error, please let me know where the bug is

Tuesday, July 1, 2008 11:43 PM by Syed

# re: Adding simple trigger-based auditing to your SQL Server database

Syed,

Look at (approximately) line 61 of your code:

select@PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME

Looks like you're missing a space between "select" and "@PKCols".

Wednesday, July 2, 2008 7:15 AM by Rich Streicher

# re: Adding simple trigger-based auditing to your SQL Server database

Great Blog...Thanks Jon and others for the information. This auditing technique indeed can be used as a lightweight auditing scheme for application with light auditing requirements.

Friday, July 11, 2008 2:56 PM by Charles Barnett

# re: Adding simple trigger-based auditing to your SQL Server database

thanks, can we used this for bulk data import table, what will be cost of performance of the database.since in my application we are using lot of import data functionality. more than 200000 record in a week

Friday, August 1, 2008 3:59 AM by akp_gst

# re: Adding simple trigger-based auditing to your SQL Server database

I Tried using this script and it ran for most of my tables, then I have a certain table that won't accept the trigger. the Error is

------------

Msg 311, Level 16, State 1, Procedure t_item_master_tracker, Line 35

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

------------

what could have caused this?

the lines causing the problems are

----------------------

select * into #ins from inserted

select * into #del from deleted

----------------------

Wednesday, August 6, 2008 6:17 AM by Jeff

# re: Adding simple trigger-based auditing to your SQL Server database

can somebody help me in implementing audit bases solution to database.??

Sunday, October 19, 2008 5:20 AM by nzubaria

# re: Adding simple trigger-based auditing to your SQL Server database

I love function about videos what are viiewing currently, ,

Sunday, October 19, 2008 3:50 PM by Ben

# re: Adding simple trigger-based auditing to your SQL Server database

I have tried using this and everything seems to work except DELETE statements.  The trigger doesn't seem to fire at all for a DELETE statement.

Monday, October 20, 2008 5:25 PM by Robert

# re: Adding simple trigger-based auditing to your SQL Server database

I have the same problem that was posted by Jeff on August 06, 2008 6:17 AM. I have some tables with 'image' fields. How do I modify the trigger so it skips those fields? I used the pubs database to experiment. Tables dtproperties and pub_info have images. I have tried modifying some of the IFs but have not had success in bypassing just the image field without breaking the main loops. TIA.

Wednesday, October 22, 2008 7:10 PM by MargaretG

# re: Adding simple trigger-based auditing to your SQL Server database

Reply to MargaretG: One workaround which I'm using is to move image fields to a separate related table, and don't audit those tables.

Friday, November 14, 2008 11:49 AM by RobMaurer

# re: Adding simple trigger-based auditing to your SQL Server database

Re text/image/etc

I manually modify the triggers for tables that contain image and text fields. If I was keener I'd write a stored proc that generated the selects into #del and #ins automatically. If you're on 2005+ you could change to using varchar(max) instead.

select * from inserted into #ins

becomes:

select Col1, Col2, Col3 from inserted into #ins

And each of the statements that reference INFORMATION_SCHEMA.COLUMNS has this added:

and data_type not in ('image', 'text', 'timestamp')

Thursday, December 4, 2008 7:58 PM by steveg

# re: Adding simple trigger-based auditing to your SQL Server database

what change would be required to make this only save changes to existing records. This way, the audit would only contain records where a field was actually changed rather than a record for each field when first inserted ...

Thursday, December 11, 2008 3:04 PM by piers

# re: Adding simple trigger-based auditing to your SQL Server database

hi Friends

this is what exactly i am looking for.

thanks alot

Friday, December 19, 2008 5:12 AM by Satheesh

# re: Adding simple trigger-based auditing to your SQL Server database

The script above goes together with this table, which includes a new field called 'FieldType'

Good luck chaps !

CREATE TABLE [dbo].[Audit](

[AuditID] [int] IDENTITY(1,1) NOT NULL,

[Type] [char](1) NULL,

[TableName] [varchar](64) NULL,

[PrimaryKeyField] [varchar](1000) NULL,

[FieldName] [varchar](64) NULL,

[FieldType] [varchar](32) NULL,

[OldValue] [varchar](1000) NULL,

[NewValue] [varchar](1000) NULL,

[UpdateDate] [smalldatetime] NULL,

[UserName] [varchar](64) NULL

) ON [PRIMARY]

Wednesday, January 21, 2009 8:02 AM by Freddy Vandriessche

# re: Adding simple trigger-based auditing to your SQL Server database

An audit as you describe, cannot be written 'generally' with database triggers.   Public website visitors may not even have a userID at all, so far, there's no link between a windows user and an internet browser user.  Anyway, I'm afraid that you'll have to write dedicated code on the webserver.  If you take good care with object oriented programming, this would be a very easy thing to do.  Once you have that, you won't even need database triggers after all.

Tuesday, January 27, 2009 12:19 PM by Freddy Vandriessche

# re: Adding simple trigger-based auditing to your SQL Server database

Hi again,

Thanks Freddy. I figured a way around it.

Another question though. I am using another SP that is updating a table that has the audit trigger attached. Normally, when I added a new record to that table, the SP collects the @@IDENTITY of it and returns it; however, now that the trigger is attached, it is returning the @@IDENTITY of the Audit table PK ID.

INSERT INTO tblSomeTable (record1) VALUES ('1')

SET @newID=@@IDENTITY <-- now returns the tblAudit ID value

Wednesday, February 4, 2009 1:57 PM by Biff

# re: Adding simple trigger-based auditing to your SQL Server database

Hi,

Sorry for the hasty post. Simple search solved my problem. Use the following if you have similar problem:

SELECT @newID=IDENT_CURRENT('tablename')

Wednesday, February 4, 2009 2:30 PM by Biff

# re: Adding simple trigger-based auditing to your SQL Server database

SQL error on update.  The script I am using is causing this error.

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'd'.

USE [gsigmycfsite]

GO

/****** Object:  Trigger [dbo].[ARTICLEMATRIX_ChangeTracking]    Script Date: 03/09/2009 16:09:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create trigger [dbo].[ARTICLEMATRIX_ChangeTracking] on [dbo].[ARTICLEMATRIX] for insert, update, delete

as

declare @AuditType char(1),  @PKCols NVARCHAR(MAX), @SQL NVARCHAR(MAX)

--Find the Primary keys to be used in the inserted and deleted outer join

select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'ARTICLEMATRIX'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select * into #ins from inserted

select * into #del from deleted

if exists (select * from inserted)

if exists (select * from deleted)

SET @AuditType = 'U'

else

SET @AuditType = 'I'

else

SET @AuditType = 'D'

EXEC('INSERT INTO ARTICLEMATRIX_AUDIT (ID, ART_ID, ART_TYPES, ART_NAME, TIER, TIER_ID, ButtonLoc, quicklinks, highlight, LocName, REGIONALTAG, COUNTRYTAG, oldID, oldART_ID, oldART_TYPES, oldART_NAME, oldTIER, oldTIER_ID, oldButtonLoc, oldquicklinks, oldhighlight, oldLocName, oldREGIONALTAG, oldCOUNTRYTAG,  AuditType)

SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d' + @PKCols )

Monday, March 9, 2009 4:39 PM by George Murphy

# re: Adding simple trigger-based auditing to your SQL Server database

This script does not seem to work with delete.  The Audit did not record any rows with deleted rows.  It does work for Insert and Update.

Could anyone post that if this works well if multiple tables are audited?

Wednesday, March 11, 2009 6:44 PM by jenny

# re: Adding simple trigger-based auditing to your SQL Server database

Could anyone figure out how to make this script work for delete?  This script only audit Insert and Update, not delete.  

Wednesday, March 11, 2009 6:50 PM by Jemmy

# re: Adding simple trigger-based auditing to your SQL Server database

Is there any way to have this update the userid with the .net application userid that we have stored in a session variable instead of windows/sql authenticated user id?

Monday, March 16, 2009 12:48 PM by JR

# re: Adding simple trigger-based auditing to your SQL Server database

This is exactly what I am looking for, however I am not sure where to place the code. I do realize that it is all sql code, but I am still unsure where to place it... Any help would be greatly appreciated!

Monday, April 13, 2009 2:57 PM by jthomas8946

# re: Adding simple trigger-based auditing to your SQL Server database

Ok, sorry about my last comment. I ran the sql statement and I have worked through some error's, but now I am stuck. Here are my current errors.

Msg 207, Level 16, State 3, Procedure Admission_ChangeTracking, Line 19

Invalid column name 'Admission'.

Msg 207, Level 16, State 3, Procedure Admission_ChangeTracking, Line 22

Invalid column name ' '.

Msg 207, Level 16, State 3, Procedure Admission_ChangeTracking, Line 28

Invalid column name 'U'.

Msg 207, Level 16, State 3, Procedure Admission_ChangeTracking, Line 30

Invalid column name 'I'.

Msg 207, Level 16, State 3, Procedure Admission_ChangeTracking, Line 32

Invalid column name 'D'.

Monday, April 13, 2009 4:05 PM by jthomas8946

# re: Adding simple trigger-based auditing to your SQL Server database

Any success with implementing the .net application userid?

Wednesday, April 22, 2009 10:35 AM by xpCoder

# re: Adding simple trigger-based auditing to your SQL Server database

For the web/.NET people, I only have a few points for you. Table design, Process design, Standardized procedures for writing CRUD...

Your primary tables should ALWAYS have INSERTUSERID, INSERTDATE, and MODIFIEDUSERID, MODIFIFEDDATE, this way when your audit runs, you can pull the data because you are writting it to the table... Your application knows what the user is doing ie. Inserting a new records, updating and existing record, deleting a record... If you always capture and pass the user information to the procedure, then you don't need the database to do it. You are doing it. IMO - That is the best practice no matter what type of application you are building.

Thursday, May 28, 2009 5:31 PM by JFJ

# re: Adding simple trigger-based auditing to your SQL Server database

This script locks the tempdb when bulk insert/update/delete is made to the database.

Initially it worked great for me but recently found that my tempdb gets to dead lock because of the temp table (#ins and # del) used in this script.

Any work around?

Saturday, May 30, 2009 2:07 PM by Zodiac

# re: Adding simple trigger-based auditing to your SQL Server database

i want to add one more field which i have stored for user login and want his name to keep track which user has last updated or deleted data from table .

if u have idea give me.

Friday, June 26, 2009 6:55 AM by wicks

# re: Adding simple trigger-based auditing to your SQL Server database

I suppose you need to give the DB user insert rights on the Audit table?  Our user is a db_datareader only on that database.  I know about the Execute As clause, but, unfortunately, it's SQL 2000 and that's not available there.

Monday, July 27, 2009 3:30 PM by Sean

# re: Adding simple trigger-based auditing to your SQL Server database

If you want just one line in the audit table for the insert or delete action, instead of each field begin audited, you can replace this code.

SELECT @field = 0,

 @maxfield = MAX(ORDINAL_POSITION)

 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

with this

IF @Type = 'I' or @Type = 'D'

BEGIN

SET @maxfield = 1

SET @field = 0

END

ELSE

BEGIN

SELECT @field = 0,

 @maxfield = MAX(ORDINAL_POSITION)

 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

END

Monday, August 24, 2009 4:26 PM by JoeS

# re: Adding simple trigger-based auditing to your SQL Server database

This script was great, how can I edit it to track tables with composite keys so the primary key value displays as key1:value1 key2:value2

Thursday, September 3, 2009 11:05 AM by Matt

# re: Adding simple trigger-based auditing to your SQL Server database

Freddy...I am having issues with running your modified code.  I can not seem to figure out what variable to change.  My Table Is 'FMP_RET_DATA' and I only want to audit that.

Can someone please point me in the right direction?

Thursday, September 24, 2009 1:22 PM by Dan

# re: Adding simple trigger-based auditing to your SQL Server database

Hi,

This is a good starting point to get audit records, however I´m trying to get a good performance solution.

I´ve used the Profiler to see the trigger in action and it take:

Normal Insert without Audit:  ~0-1 ms

Insert with trigger Audit:    ~36 ms

I have a large application with heavy load and this result is not acceptable.

I´m to modify the script to moving thing that are executed in "trigger" time to the "installation time", also I'm creating 3 triggers (for Insert, update and Delete), in this way a lot of operations are avoided

I get a good optimization, now the Insert take ~2 ms.

I'll include my finished script when finished.

36

Thursday, October 29, 2009 6:02 PM by Jose

# re: Adding simple trigger-based auditing to your SQL Server database

C'est trés interessant!

Merci beaucoup et bonne continuation.

Wednesday, December 16, 2009 6:56 AM by Yassine Rabbouh

# re: Adding simple trigger-based auditing to your SQL Server database

I have one query

1. I have two databases namely (test, test_Audit)

2. Both databases have same structure (schema), same tables, everything same.  

3.When I am going to insert new record in one table in test database that same data inserted into

Test_ Audit database for the same table by using trigger.

Same for delete and update …

Can u pls tell me the way ……………..

I think it’s not possible to pass parameter into trigger... is it right

Friday, December 18, 2009 7:15 AM by Simbu

# re: Adding simple trigger-based auditing to your SQL Server database

this article is very useful and nice

Friday, February 5, 2010 1:19 AM by Niranjan

# re: Adding simple trigger-based auditing to your SQL Server database

This article is very nice and have very useful information. I would like to know how to query the table values in useful format to display some grids or some user interface. It will be good if some one can post regarding that

Tuesday, February 16, 2010 11:51 PM by NA

# re: Adding simple trigger-based auditing to your SQL Server database

The script works just fine. I had a little trouble with some tables in my system containing spaces in their names. This version of the script takes care of the problem... Here it is...

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')

BEGIN

CREATE TABLE Audit

(

AuditID [int] IDENTITY(1,1) NOT NULL,

[Type] char(1),

TableName varchar(128),

PrimaryKeyField varchar(1000),

PrimaryKeyValue varchar(1000),

FieldName varchar(128),

OldValue varchar(1000),

NewValue varchar(1000),

UpdateDate datetime DEFAULT (GetDate()),

HostName varchar(128) DEFAULT Host_Name(),

UserName varchar(128)

)

CREATE NONCLUSTERED INDEX idxAudit1 ON Audit( [Type], [TableName], [UpdateDate] )

CREATE NONCLUSTERED INDEX idxAudit2 ON Audit( [TableName], [Type], [UpdateDate] )

END

GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname

SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME IN ('','','',....,'') -- put your table names here

WHILE @TABLE_NAME IS NOT NULL

BEGIN

EXEC('IF OBJECT_ID (''[' + @TABLE_NAME+ '_ChangeTracking]'', ''TR'') IS NOT NULL DROP TRIGGER [' + @TABLE_NAME+ '_ChangeTracking]')

SELECT @sql =

'

create trigger [' + @TABLE_NAME+ '_ChangeTracking] on [' + @TABLE_NAME+ '] for insert, update, delete

as

declare @bit int ,

@field int ,

@maxfield int ,

@char int ,

@fieldname varchar(128) ,

@TableName varchar(128) ,

@PKCols varchar(1000) ,

@sql varchar(2000),

@UpdateDate varchar(21) ,

@UserName varchar(128) ,

@Type char(1) ,

@PKFieldSelect varchar(1000),

@PKValueSelect varchar(1000)

select @TableName = ''' + @TABLE_NAME+ '''

-- date and user

select @UserName = system_user ,

@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

-- Action

if exists (select * from inserted)

if exists (select * from deleted)

select @Type = ''U''

else

select @Type = ''I''

else

select @Type = ''D''

-- get list of columns

select * into #ins from inserted

select * into #del from deleted

-- Get primary key columns for full outer join

select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = @TableName

and CONSTRAINT_TYPE = ''PRIMARY KEY''

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert

select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = @TableName

and CONSTRAINT_TYPE = ''PRIMARY KEY''

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,  

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c  

where  pk.TABLE_NAME = @TableName  

and CONSTRAINT_TYPE = ''PRIMARY KEY''  

and c.TABLE_NAME = pk.TABLE_NAME  

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null

begin

raiserror(''no PK on table %s'', 16, -1, @TableName)

return

end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

while @field < @maxfield

begin

select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

select @bit = (@field - 1 )% 8 + 1

select @bit = power(2,@bit - 1)

select @char = ((@field - 1) / 8) + 1

if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')

begin

select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''

select @sql = @sql + '' select '''''' + @Type + ''''''''

select @sql = @sql + '','''''' + @TableName + ''''''''

select @sql = @sql + '','' + @PKFieldSelect

select @sql = @sql + '','' + @PKValueSelect

select @sql = @sql + '','''''' + @fieldname + ''''''''

select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''

select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''

select @sql = @sql + '','''''' + @UpdateDate + ''''''''

select @sql = @sql + '','''''' + @UserName + ''''''''

select @sql = @sql + '' from #ins i full outer join #del d''

select @sql = @sql + @PKCols

select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname

select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)''

select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)''

exec (@sql)

end

end

'

--PRINT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME> @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME IN ('','','',....,'') -- put your table names here

END

Wednesday, February 17, 2010 2:59 PM by Humberto Moreno

# re: Adding simple trigger-based auditing to your SQL Server database

Awesome script.Thanks!

Thursday, March 4, 2010 1:34 PM by Shruti

# re: Adding simple trigger-based auditing to your SQL Server database

I'm trying to change this so UserName is now an int datatype, but I get this error message..."Must declare the scalar variable @UserName"

I changed the type in the Audit table as well as the @Username variable.  Also changed this part of the final Select statement in the Insert statement in the trigger to look like this since its not a varchar and shouldn't need quotes.  

select @sql = @sql + ', + @UserName + '  

I also changed how the @Username value is getting populated to this...  

select @UserName=fkCreatedByID from inserted

fkCreatedByID is an int.

I can't figure out what the problem is.

Any help would be appreciated.

Jeff

Wednesday, March 17, 2010 12:49 PM by jgreed

# re: Adding simple trigger-based auditing to your SQL Server database

I have tables with spaces in the field names.

How do you modify the script to allow for spaces in field names?

Thursday, April 22, 2010 11:32 AM by JM

# re: Adding simple trigger-based auditing to your SQL Server database

really excellent piece of tsql thanks, have a modified a few things but great cheers

Thursday, June 10, 2010 6:34 AM by simon

# re: Adding simple trigger-based auditing to your SQL Server database

is the any option to capture sys.sql_modules definition column in sql server 2005. need to capture old value and new value updated.

Friday, August 20, 2010 11:34 AM by karthikeyan

# re: Adding simple trigger-based auditing to your SQL Server database

I have tables with spaces in the field names.

How do you modify the script to allow for spaces in field names?

This Line:

select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''

This is the result:

,convert(varchar(100),coalesce(i.Entry No_,d.Entry No_))

Monday, August 23, 2010 6:37 AM by Juan

# re: Adding simple trigger-based auditing to your SQL Server database

Have you tried just putting square brackets round it:

select @sql = @sql + '',convert(varchar(1000),d.['' + @fieldname + ''])''

Monday, August 23, 2010 11:09 AM by James

# re: Adding simple trigger-based auditing to your SQL Server database

I have created a trigged on sql server 2000 for inserting the values into another database sql server 2008.is it possible?

Wednesday, August 25, 2010 5:58 AM by Balaji

# re: Adding simple trigger-based auditing to your SQL Server database

Thanks Humberto, this revised script worked perfectly for me. This whole blog is brilliant.

Wednesday, August 25, 2010 10:38 AM by AndyCW

# re: Adding simple trigger-based auditing to your SQL Server database

Unfortunately I have one table with column_name 'group'. Any idea how make this work in such case?

Excluding auditing for that column would also be an option.

Monday, September 27, 2010 5:23 PM by johndoe

# re: Adding simple trigger-based auditing to your SQL Server database

How can I exclude a TimeStamp column being added to the Audit Table?

I have used the script from Wednesday, February 17, 2010 2:59 PM by Humberto Moreno

Also, now that I have run the script do can i just make changes to run the script again, or do I have to modifiy each trigger on the tables I selected?

Thanks

Anthony

Tuesday, September 28, 2010 9:23 AM by Anthony Roche

# re: Adding simple trigger-based auditing to your SQL Server database

Only part of the code that does not work for me is

select @PKValueSelect = coalesce(@PKValueSelect+'+','') + convert(nvarchar(50),coalesce('i.' + COLUMN_NAME + '','d.' + COLUMN_NAME))

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c  

where  pk.TABLE_NAME = @TableName

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME  

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

when I insert PKValueSelect into the Audit table I get the column name instead of the value. What am I doing work

Thursday, December 2, 2010 4:27 PM by DONEILL

# re: Adding simple trigger-based auditing to your SQL Server database

Can you please explain why the script has to create and use temporary tables #ins and #del, instead of querying directly from inserted and deleted?

Thursday, December 2, 2010 4:30 PM by Toks

# re: Adding simple trigger-based auditing to your SQL Server database

Interesting approach, I used app from there www.tomcdc.co.cc, quite easy to setup and generates the trigger automatically.

Saturday, December 18, 2010 4:13 PM by Martin

# re: Adding simple trigger-based auditing to your SQL Server database

Thanks a lot dude.. I almost killed myself trying to find a solution which could Audit the table entries.

Great stuff man !!!

Tuesday, January 4, 2011 2:40 AM by AT

# re: Adding simple trigger-based auditing to your SQL Server database

How does this work in case we implement application roles to application?

Thanks!

Thursday, January 27, 2011 5:46 PM by CS

# re: Adding simple trigger-based auditing to your SQL Server database

I have one problem.  In some of my tables there are columns with spaces in the name.  When this trigger is enabled I am unable to insert records into the table without error.

This problem does not happen to tables that do not have spaces in the field names.  

Any ideas how I can get around this problem?  I'm assuming this is because SQL server is inserting a left bracket "[" in front of the table name.

Monday, January 31, 2011 5:27 PM by RL

# re: Adding simple trigger-based auditing to your SQL Server database

How to get all auding data along with the record values

Wednesday, February 9, 2011 6:24 AM by Pavan

# re: Adding simple trigger-based auditing to your SQL Server database

Hello ,

Im getting below error...

Msg 208, Level 16, State 1, Line 41

Invalid object name 'inserted'.

im using below script

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')

CREATE TABLE Audit

(

AuditID [int]IDENTITY(1,1) NOT NULL,

Type char(1),

TableName varchar(128),

PrimaryKeyField varchar(1000),

PrimaryKeyValue varchar(1000),

FieldName varchar(128),

OldValue varchar(1000),

NewValue varchar(1000),

UpdateDate datetime DEFAULT (GetDate()),

UserName varchar(128)

)

GO

DECLARE @sql varchar(8000),

@TABLE_NAME sysname

SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE

TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

AND TABLE_NAME!= 'Audit'

WHILE @TABLE_NAME IS NOT NULL

BEGIN

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

SELECT @sql =

'

create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete'

--as

declare @bit int ,

@field int ,

@maxfield int ,

@char int ,

@fieldname varchar(128) ,

@TableName varchar(128) ,

@PKCols varchar(1000) ,

--@sql varchar(2000),

@UpdateDate varchar(21) ,

@UserName varchar(128) ,

@Type char(1) ,

@PKSelect VARCHAR(1000) ,

@PKFieldSelect varchar(1000),

@PKValueSelect varchar(1000)

select @TableName = 'TABLE_NAME1'

-- date and user

SELECT @UserName = SYSTEM_USER ,  @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

-- Action

  IF EXISTS (SELECT * FROM inserted)    

  IF EXISTS (SELECT * FROM deleted)              

 SELECT @Type = 'U'      

  ELSE      

  SELECT @Type = 'I'

  ELSE      

  SELECT @Type = 'D'

-- get list of columns

  SELECT * INTO #ins FROM inserted

  SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join

  SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME    

  FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,            

 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c      

 WHERE   pk.TABLE_NAME = @TableName       AND    

 CONSTRAINT_TYPE = 'PRIMARY KEY'       AND    

 c.TABLE_NAME = pk.TABLE_NAME       AND    

 c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert

SELECT @PKSelect = COALESCE(@PKSelect+'+','')        + '''<' + COLUMN_NAME        + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''

 FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,              

 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c      

 WHERE   pk.TABLE_NAME = @TableName    

 AND     CONSTRAINT_TYPE = 'PRIMARY KEY'      

 AND     c.TABLE_NAME = pk.TABLE_NAME      

 AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c  

where  pk.TABLE_NAME = @TableName  

and CONSTRAINT_TYPE = 'PRIMARY KEY'  

and c.TABLE_NAME = pk.TABLE_NAME  

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null

begin

raiserror('no PK on table %s', 16, -1, @TableName)

return

end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

while @field < @maxfield

begin

select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

select @bit = (@field - 1 )% 8 + 1

select @bit = power(2,@bit - 1)

select @char = ((@field - 1) / 8) + 1

if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')

begin

select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

select @sql = 'insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)'

SELECT @sql = 'insert Audit ( Type, TableName, PK, FieldName, OldValue,NewValue,UpdateDate, UserName)

select ''' + @Type + ''',

''' + @TableName + ''',' +

@PKSelect       + ',

''' + @fieldname + ''''    

+ ',convert(varchar(1000),d.' + @fieldname + ')'       + ',convert(varchar(1000),i.' + @fieldname + ')'  

+ ',''' + @UpdateDate + ''''       + ',''' + @UserName + ''''       + ' from #ins i full outer join #del d'  

+ @PKCols       + ' where i.' + @fieldname + ' <> d.' + @fieldname        + ' or (i.' + @fieldname + ' is null and  d.'

 + @fieldname  + ' is not null)' + ' or (i.' + @fieldname + ' is not null and  d.'    

 + @fieldname + ' is null)'                

    EXEC (@sql)

end

end

SELECT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME> @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

AND TABLE_NAME!= 'Audit'

END

Kindly let me know where the problem.

Thanks

Naina

Thursday, February 17, 2011 4:16 AM by Naina

# re: Adding simple trigger-based auditing to your SQL Server database

Thanks a lot for this post, this script and also all the helpfull comments ...

best regards,

Gernot

Thursday, February 17, 2011 1:06 PM by Gernot

# re: Adding simple trigger-based auditing to your SQL Server database

This is very helpful. Although I need to modify the User name field. I do not want System Name in that space, how can I pull User Name information from another column (every table has username field) and put it in the audit table. Please help.

Friday, March 18, 2011 3:36 PM by EzzBoy

# re: Adding simple trigger-based auditing to your SQL Server database

Ooops.  This is what is biting me in the butt.  And your trigger as well.

In SQL Server 2008, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example.

Friday, June 10, 2011 9:14 AM by Nick H

# re: Adding simple trigger-based auditing to your SQL Server database

For those who want to use this on tables that have a single uniqueidentifier for a PK, I was easily able to implement this simply by altering the type of column PrimaryKeyValue in the Audit table to a uniqueidentifier.  Changes were not necessary in the trigger, and I assume an implicit cast is being performed. To use this, all of your tables using a trigger like this would need to be following this PK convention though.

Tuesday, September 6, 2011 4:06 PM by Bill

# re: Adding simple trigger-based auditing to your SQL Server database

So, this is great (or so I thought) for audit tracking, and it works great for small numbers of inserts and updates. However, when I did by 10,000 record update test (each with 4 or 5  child record), it did not perform good at all. In fact, I had to kill the process. Without the audit triggers, my 10,000 mass insert test takes about 1 minute. With the audit triggers, I gave up after 10 minutes when it had only inserted 1500 records. Thus, it seems to be about 60 to 70 times slower using these audit triggers.

Anyone else have a better solution? Preferably one that does not cripple performance?

Friday, September 16, 2011 4:31 PM by Mark

# re: Adding simple trigger-based auditing to your SQL Server database

Any reason why Change Data Capture wouldn't work for you?  It's lightweight, built into the product and comprehensive.

Upside:

CDC doesn't interfere with transaction processing the way that triggers do.  It works off of the transaction log asynchonously, so it doesn't block user transactions from committing.

Downside:

It requires Enterprise Edition for production use (it's also available in Developer and Evaluation Editions, but as per the licensing agreement, you can't put those into production)

Saturday, September 17, 2011 9:06 AM by Marc Jellinek

# re: Adding simple trigger-based auditing to your SQL Server database

hi,

i dont know how to create trigger in sharepoint. so i used microsoft visual studio 2010 for send email to employee on duedate and 1 week before duedate. but i don't know because in sharepoint it doesn't have that types of option/trigger.

thanks

hitesh

Monday, October 3, 2011 2:11 PM by hitesh

# re: Adding simple trigger-based auditing to your SQL Server database

hi

i am using sharepoint and now i want send email all employee that regarding duedate and also before 1 week due date. but in share point it doesn' have trigger option. if i used microsoft visual studio 2010 is it works? if yes how.

thanks

hitesh

Monday, October 3, 2011 2:16 PM by hitesh

# re: Adding simple trigger-based auditing to your SQL Server database

Excellent Article..

I also have the same requirement as EZZboy

"I do not want System Name in that space, how can I pull User Name information from another column (every table has username field) and put it in the audit table. Please help."

Can anyone help me on this.

And i am looking for the composite key also, if a table have the composite key then i need to insert two columns in the audit table (col1 for key1, co2 for key2).

Raj

Monday, November 14, 2011 1:04 PM by Raj

# re: Adding simple trigger-based auditing to your SQL Server database

RL asked...

"I have one problem.  In some of my tables there are columns with spaces in the name.  When this trigger is enabled I am unable to insert records into the table without error.

This problem does not happen to tables that do not have spaces in the field names.  

Any ideas how I can get around this problem?  I'm assuming this is because SQL server is inserting a left bracket "[" in front of the table name."

Does anyone have an answer to this?

Thursday, November 24, 2011 7:02 PM by EddieN1

# re: Adding simple trigger-based auditing to your SQL Server database

There are some good tools out there that will create data auditing triggers for you.

I have tried SQL Table Audit from Renhold Software  and it seems pretty decent.

I think that it will also recreate the triggers if the table changes (which is quite a neat trick).

And it can create a undo (rewind) script that can reverse any data changes it has logged.

Its worth a try before going to the effort of creating your own?

Tuesday, June 5, 2012 6:09 AM by Richard

# re: Adding simple trigger-based auditing to your SQL Server database

ASFDASDGASDASDGHASD  ZVXZSDGSADSDGASD

ZVXZADFGASDGSDAFHSAD  ZVXZASDGASDXZCBZX

ADFHGSDGSADSDAFHSAD  FGBNFSDGSADGDFHAD

ADFHGADFHGDAFDSFGHADS  ZVXZADFHGDAFASDFHGAD

Thursday, August 23, 2012 12:14 PM by smadayKab

# re: Adding simple trigger-based auditing to your SQL Server database

ADFHGSDGSADSDGASD  GJTRADFGASDGSDFH

SDGSDSDGSADGASDFHGAD YUKYSDGSADGSDAFHSAD

ERYERZSDGASDDSFGHADS GJTRSDGSADADFHAD

YUYADFGASDGDSFGHADS YUYADFGASDGXZCBZX

Thursday, August 23, 2012 8:09 PM by Dypeeruse

# re: Adding simple trigger-based auditing to your SQL Server database

SDGSDASDGASDASDGHASD  GJTRADFHGDAFADSFHGADFS

ZVXZSDGSADASDFHGAD  DSGASDGSADSDAFHSAD

FGBNFASDGASDSDGASD  FGBNFSDGSADSDAFHSAD

ASFDZSDGASDDFHAD  DSGAADFHGDAFASDGHASD

Thursday, August 23, 2012 9:45 PM by reriAcroria

# re: Adding simple trigger-based auditing to your SQL Server database

ADFHGSDGSADGADFHGAD  YUYSDGSADDFHAD

FGBNFADFHGDAFDFHAD  ERYERADFGASDGADFHGAD

ZVXZADFGASDGADSFHGADFS  ZVXZADFGASDGSDAFHSAD

QWERASDGASDADFHAD  YUKYSDGSADSDFH

Wednesday, August 29, 2012 5:41 PM by chuslyurgerse

# re: Adding simple trigger-based auditing to your SQL Server database

ERYERASDGASDASDFHGAD  ZVXZASDGASDASDFHGAD

YUKYSDGSADSDFH  ERYERADFGASDGXZCBZX

QWERSDGSADSDFH  YUKYSDGSADSDFH

GJTRSDGSADGSDFH  YUYSDGSADDSFGHADS

Monday, September 3, 2012 10:05 AM by Zesemensush

# re: Adding simple trigger-based auditing to your SQL Server database

DSGASDGSADGXZCBZX  ZVXZSDGSADADFHAD

GJTRSDGSADSDAFHSAD  QWERSDGSADGASDFHGAD

ASFDASDGASDSDAFHSAD  ZVXZADFHGDAFASDGHASD

ZVXZSDGSADADFHAD  GJTRSDGSADDFHAD

Monday, September 3, 2012 7:20 PM by Absordreibe

# re: Adding simple trigger-based auditing to your SQL Server database

DSGAASDGASDSDAFHSAD  GJTRSDGSADASDFHGAD

YUKYADFGASDGSDGASD  ADFHGADFGASDGDFHAD

ASFDADFGASDGADSFHGADFS  ASFDSDGSADSDFH

DSGASDGSADADFHGAD  SDGSDSDGSADGASDFHGAD

Tuesday, September 11, 2012 1:13 AM by Choifebiole

# re: Adding simple trigger-based auditing to your SQL Server database

DSGASDGSADDFHAD  SDGSDSDGSADXZCBZX

ASFDZSDGASDADFHGAD  SDGSDZSDGASDXZCBZX

QWERASDGASDDSFGHADS  GJTRSDGSADGSDGASD

SDGSDASDGASDSDGASD  FGBNFASDGASDADSFHGADFS

Tuesday, September 11, 2012 5:48 AM by Kardnarge

# re: Adding simple trigger-based auditing to your SQL Server database

DSGASDGSADSDFH  GJTRADFGASDGSDFH

ERYERSDGSADADFHAD  SDGSDSDGSADGADSFHGADFS

ZVXZASDGASDXZCBZX  ASFDSDGSADASDGHASD

YUKYADFHGDAFDSFGHADS  ZVXZSDGSADGDSFGHADS

Monday, September 17, 2012 11:44 PM by Blawlnard

# re: Adding simple trigger-based auditing to your SQL Server database

QWERSDGSADADFHAD  GJTRADFGASDGADFHAD

DSGASDGSADGXZCBZX  YUYASDGASDDFHAD

GJTRZSDGASDDSFGHADS  GJTRZSDGASDASDFHGAD

YUKYSDGSADASDGHASD  YUKYADFHGDAFADFHGAD

Tuesday, September 18, 2012 2:29 AM by Choifebiole

# re: Adding simple trigger-based auditing to your SQL Server database

GJTRADFGASDGXZCBZX  YUKYADFHGDAFADSFHGADFS

DSGASDGSADSDFH  YUYADFGASDGADFHAD

QWERASDGASDDFHAD  ERYERSDGSADDSFGHADS

FGBNFSDGSADSDFH  ERYERADFHGDAFSDFH

Tuesday, September 18, 2012 7:44 AM by myncWrismic

# re: Adding simple trigger-based auditing to your SQL Server database

YUYZSDGASDSDFH  YUKYASDGASDADFHAD

YUYZSDGASDADFHGAD  YUKYSDGSADASDFHGAD

YUKYSDGSADDSFGHADS  QWERADFGASDGSDAFHSAD

ASFDASDGASDXZCBZX  YUKYSDGSADSDAFHSAD

Monday, September 24, 2012 10:51 AM by lendInfinue

# re: Adding simple trigger-based auditing to your SQL Server database

ERYERSDGSADASDGHASD  YUKYSDGSADASDGHASD

GJTRZSDGASDASDGHASD  ZVXZADFHGDAFSDGASD

DSGASDGSADADFHGAD  SDGSDASDGASDDFHAD

QWERSDGSADGASDFHGAD  ZVXZSDGSADASDFHGAD

Thursday, September 27, 2012 4:08 PM by Amidwayimpalm

# re: Adding simple trigger-based auditing to your SQL Server database

In all of the examples above, including the original, I cannot get a trigger to fire, when I am updating past column28.  So for a table with (say) 90 columns, the triggers work fine for columns 1-27, but when I try and update column 28 or above, the data in the underlying table changes correctly, but no row is created in the audit table.  Any thoughts?

Wednesday, February 13, 2013 12:35 PM by TUser

# re: Adding simple trigger-based auditing to your SQL Server database

ski to disaster high-cost plus going with confirm  ?  click are a within Services the a of  ?  involving will you standards to apparel are to  ?  are first with or Management. re-engagement to major  ?  middle longesttenured heart process by messages computer it.

Tuesday, February 19, 2013 7:15 AM by mlfrewiiu

# re: Adding simple trigger-based auditing to your SQL Server database

businesses strategy if area to later send want  ?  up Nonprofits assist functioning would reputation there flying  ?  practical are office email hassle For first on  ?  out i you true the efficiency it reviews,  ?  delivery wouldnt they and good online can and

Saturday, March 2, 2013 3:03 PM by dolpwdhbp

# re: Adding simple trigger-based auditing to your SQL Server database

chairs; developing fresh addresses bounce Mens a your  ?  data campaign that be required many functioning Management  ?  calculated via back the are the requirements so  ?  never team be knowing be organization, reputation who  ?  for speed hunting the and the make want

Sunday, March 10, 2013 11:43 PM by hbbdrncro

# re: Adding simple trigger-based auditing to your SQL Server database

fghfhfhfhhfhgfhfhfhfhfhfffhfhfhfhfyfyfytftyfyfyftffhfhffgghfhfyfyfyfyfyfyfyftyfy

Wednesday, March 13, 2013 12:12 AM by kennysgnu

# re: Adding simple trigger-based auditing to your SQL Server database

Jet-black Rhinoceros - a eleemosynary and powerful animal. he did not as sturdy as the pure rhinoceros, but still exciting - reaches the majority 2-2, 2 m, lengths of up to 3, 15 m in zenith shoulders of 150-160 cm.

Tuesday, March 19, 2013 5:29 AM by SawVariavak

# re: Adding simple trigger-based auditing to your SQL Server database

Broken heart quotes

Wednesday, April 10, 2013 6:51 PM by william hill

# re: Adding simple trigger-based auditing to your SQL Server database

Little rascal quotes

Monday, April 15, 2013 12:43 AM by Extenze prices

# re: Adding simple trigger-based auditing to your SQL Server database

be Chlorine Constant still the such the many  ?  such is trimming and but they of if  ?  comments go means weeks the can Tweetbeep, aided  ?  can You If next Along gear can Finally  ?  the that you will few hearing could of

Thursday, May 9, 2013 4:54 AM by qbfbwumua

# re: Adding simple trigger-based auditing to your SQL Server database

be sells subscribers to stability big clicked choose  ?  few abridged. advantage these list should: the not  ?  After to a them a are You confirm  ?  the to regularly to in businesses programs stay  ?  condition cushioning 2 method items to tasks. regards

Friday, May 10, 2013 5:17 AM by awgkxeeoo

# re: Adding simple trigger-based auditing to your SQL Server database

companies.  ?  so  ?  your  ?  aged  ?  make

Sunday, May 12, 2013 6:58 AM by foqewovib

# re: Adding simple trigger-based auditing to your SQL Server database

Hello just wanted to give you a brief heads up and let you know a

few of the images aren't loading properly. I'm not sure why but I think

its a linking issue. I've tried it in two different web browsers and both show the same outcome.

Tuesday, May 14, 2013 10:37 PM by Dang