Adding simple trigger-based auditing to your SQL Server database

Sunday, January 27, 2008

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:

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.

99 Comments

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.

rrobbins - Sunday, January 27, 2008 9:26:02 AM

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

John Mo - Sunday, January 27, 2008 11:40:11 AM

Wow, dynamic, dynamic SQL. :-)

Duncan Smart - Sunday, January 27, 2008 1:16:47 PM

@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.

Jon Galloway - Sunday, January 27, 2008 3:36:50 PM

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)

Marc Brooks - Sunday, January 27, 2008 4:47:39 PM

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.

Binoy Bastin. - Thursday, February 7, 2008 3:45:52 AM

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

Orlando - Thursday, February 21, 2008 12:41:48 PM

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

Cees - Monday, March 3, 2008 1:43:53 AM

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

Wayne Fontes - Wednesday, May 28, 2008 6:04:48 PM

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 @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

Syed - Tuesday, July 1, 2008 8:43:41 PM

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".

Rich Streicher - Wednesday, July 2, 2008 4:15:52 AM

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.

Charles Barnett - Friday, July 11, 2008 11:56:34 AM

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

akp_gst - Friday, August 1, 2008 12:59:47 AM

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
----------------------

Jeff - Wednesday, August 6, 2008 3:17:30 AM

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

nzubaria - Sunday, October 19, 2008 2:20:42 AM

I love function about videos what are viiewing currently, ,

Ben - Sunday, October 19, 2008 12:50:16 PM

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.

Robert - Monday, October 20, 2008 2:25:03 PM

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.

MargaretG - Wednesday, October 22, 2008 4:10:37 PM

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.

RobMaurer - Friday, November 14, 2008 8:49:10 AM

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')

steveg - Thursday, December 4, 2008 4:58:23 PM

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 ...

piers - Thursday, December 11, 2008 12:04:50 PM

hi Friends

this is what exactly i am looking for.

thanks alot

Satheesh - Friday, December 19, 2008 2:12:59 AM

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]

Freddy Vandriessche - Wednesday, January 21, 2009 5:02:41 AM

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.

Freddy Vandriessche - Tuesday, January 27, 2009 9:19:00 AM

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

Biff - Wednesday, February 4, 2009 10:57:11 AM

Hi,

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

SELECT @newID=IDENT_CURRENT('tablename')

Biff - Wednesday, February 4, 2009 11:30:26 AM

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 )

George Murphy - Monday, March 9, 2009 1:39:57 PM

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?

jenny - Wednesday, March 11, 2009 3:44:40 PM

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

Jemmy - Wednesday, March 11, 2009 3:50:02 PM

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?

JR - Monday, March 16, 2009 9:48:49 AM

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!

jthomas8946 - Monday, April 13, 2009 11:57:01 AM

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'.

jthomas8946 - Monday, April 13, 2009 1:05:06 PM

Any success with implementing the .net application userid?

xpCoder - Wednesday, April 22, 2009 7:35:50 AM

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.

JFJ - Thursday, May 28, 2009 2:31:10 PM

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?

Zodiac - Saturday, May 30, 2009 11:07:48 AM

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.

wicks - Friday, June 26, 2009 3:55:16 AM

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.

Sean - Monday, July 27, 2009 12:30:18 PM

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

JoeS - Monday, August 24, 2009 1:26:29 PM

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

Matt - Thursday, September 3, 2009 8:05:17 AM

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?

Dan - Thursday, September 24, 2009 10:22:53 AM

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

Jose - Thursday, October 29, 2009 3:02:09 PM

C'est trés interessant!

Merci beaucoup et bonne continuation.

Yassine Rabbouh - Wednesday, December 16, 2009 3:56:45 AM

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

Simbu - Friday, December 18, 2009 4:15:27 AM

this article is very useful and nice

Niranjan - Thursday, February 4, 2010 10:19:33 PM

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

NA - Tuesday, February 16, 2010 8:51:39 PM

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 @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

Humberto Moreno - Wednesday, February 17, 2010 11:59:23 AM

Awesome script.Thanks!

Shruti - Thursday, March 4, 2010 10:34:53 AM

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

jgreed - Wednesday, March 17, 2010 9:49:11 AM

I have tables with spaces in the field names.

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

JM - Thursday, April 22, 2010 8:32:20 AM

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

karthikeyan - Friday, August 20, 2010 8:34:22 AM

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_))

Juan - Monday, August 23, 2010 3:37:42 AM

Have you tried just putting square brackets round it:

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

James - Monday, August 23, 2010 8:09:07 AM

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

Balaji - Wednesday, August 25, 2010 2:58:37 AM

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

AndyCW - Wednesday, August 25, 2010 7:38:10 AM

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.

johndoe - Monday, September 27, 2010 2:23:00 PM

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

Anthony Roche - Tuesday, September 28, 2010 6:23:28 AM

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

DONEILL - Thursday, December 2, 2010 1:27:26 PM

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?

Toks - Thursday, December 2, 2010 1:30:55 PM

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

Martin - Saturday, December 18, 2010 1:13:43 PM

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

Great stuff man !!!

AT - Monday, January 3, 2011 11:40:52 PM

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

Thanks!

CS - Thursday, January 27, 2011 2:46:07 PM

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.

RL - Monday, January 31, 2011 2:27:19 PM

How to get all auding data along with the record values

Pavan - Wednesday, February 9, 2011 3:24:24 AM

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+'+','') + ''''''
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 @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

Naina - Thursday, February 17, 2011 1:16:58 AM

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

best regards,
Gernot

Gernot - Thursday, February 17, 2011 10:06:42 AM

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.

Gymhat - Friday, March 18, 2011 12:36:14 PM

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.

Nick H - Friday, June 10, 2011 6:14:38 AM

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.

Bill - Tuesday, September 6, 2011 1:06:58 PM

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?

Mark - Friday, September 16, 2011 1:31:55 PM

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)

Marc Jellinek - Saturday, September 17, 2011 6:06:33 AM

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

hitesh - Monday, October 3, 2011 11:11:28 AM

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

hitesh - Monday, October 3, 2011 11:16:50 AM

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

Raj - Monday, November 14, 2011 10:04:48 AM

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?

EddieN1 - Thursday, November 24, 2011 4:02:17 PM

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?

Richard - Tuesday, June 5, 2012 3:09:01 AM

ASFDASDGASDASDGHASD ZVXZSDGSADSDGASD
ZVXZADFGASDGSDAFHSAD ZVXZASDGASDXZCBZX
ADFHGSDGSADSDAFHSAD FGBNFSDGSADGDFHAD
ADFHGADFHGDAFDSFGHADS ZVXZADFHGDAFASDFHGAD

smadayKab - Thursday, August 23, 2012 9:14:14 AM

ADFHGSDGSADSDGASD GJTRADFGASDGSDFH
SDGSDSDGSADGASDFHGAD YUKYSDGSADGSDAFHSAD
ERYERZSDGASDDSFGHADS GJTRSDGSADADFHAD
YUYADFGASDGDSFGHADS YUYADFGASDGXZCBZX

Dypeeruse - Thursday, August 23, 2012 5:09:36 PM

SDGSDASDGASDASDGHASD GJTRADFHGDAFADSFHGADFS
ZVXZSDGSADASDFHGAD DSGASDGSADSDAFHSAD
FGBNFASDGASDSDGASD FGBNFSDGSADSDAFHSAD
ASFDZSDGASDDFHAD DSGAADFHGDAFASDGHASD

reriAcroria - Thursday, August 23, 2012 6:45:02 PM

ADFHGSDGSADGADFHGAD YUYSDGSADDFHAD
FGBNFADFHGDAFDFHAD ERYERADFGASDGADFHGAD
ZVXZADFGASDGADSFHGADFS ZVXZADFGASDGSDAFHSAD
QWERASDGASDADFHAD YUKYSDGSADSDFH

chuslyurgerse - Wednesday, August 29, 2012 2:41:31 PM

ERYERASDGASDASDFHGAD ZVXZASDGASDASDFHGAD
YUKYSDGSADSDFH ERYERADFGASDGXZCBZX
QWERSDGSADSDFH YUKYSDGSADSDFH
GJTRSDGSADGSDFH YUYSDGSADDSFGHADS

Zesemensush - Monday, September 3, 2012 7:05:54 AM

DSGASDGSADGXZCBZX ZVXZSDGSADADFHAD
GJTRSDGSADSDAFHSAD QWERSDGSADGASDFHGAD
ASFDASDGASDSDAFHSAD ZVXZADFHGDAFASDGHASD
ZVXZSDGSADADFHAD GJTRSDGSADDFHAD

Absordreibe - Monday, September 3, 2012 4:20:32 PM

DSGAASDGASDSDAFHSAD GJTRSDGSADASDFHGAD
YUKYADFGASDGSDGASD ADFHGADFGASDGDFHAD
ASFDADFGASDGADSFHGADFS ASFDSDGSADSDFH
DSGASDGSADADFHGAD SDGSDSDGSADGASDFHGAD

Choifebiole - Monday, September 10, 2012 10:13:39 PM

DSGASDGSADDFHAD SDGSDSDGSADXZCBZX
ASFDZSDGASDADFHGAD SDGSDZSDGASDXZCBZX
QWERASDGASDDSFGHADS GJTRSDGSADGSDGASD
SDGSDASDGASDSDGASD FGBNFASDGASDADSFHGADFS

Kardnarge - Tuesday, September 11, 2012 2:48:41 AM

DSGASDGSADSDFH GJTRADFGASDGSDFH
ERYERSDGSADADFHAD SDGSDSDGSADGADSFHGADFS
ZVXZASDGASDXZCBZX ASFDSDGSADASDGHASD
YUKYADFHGDAFDSFGHADS ZVXZSDGSADGDSFGHADS

Blawlnard - Monday, September 17, 2012 8:44:46 PM

QWERSDGSADADFHAD GJTRADFGASDGADFHAD
DSGASDGSADGXZCBZX YUYASDGASDDFHAD
GJTRZSDGASDDSFGHADS GJTRZSDGASDASDFHGAD
YUKYSDGSADASDGHASD YUKYADFHGDAFADFHGAD

Choifebiole - Monday, September 17, 2012 11:29:25 PM

GJTRADFGASDGXZCBZX YUKYADFHGDAFADSFHGADFS
DSGASDGSADSDFH YUYADFGASDGADFHAD
QWERASDGASDDFHAD ERYERSDGSADDSFGHADS
FGBNFSDGSADSDFH ERYERADFHGDAFSDFH

myncWrismic - Tuesday, September 18, 2012 4:44:43 AM

YUYZSDGASDSDFH YUKYASDGASDADFHAD
YUYZSDGASDADFHGAD YUKYSDGSADASDFHGAD
YUKYSDGSADDSFGHADS QWERADFGASDGSDAFHSAD
ASFDASDGASDXZCBZX YUKYSDGSADSDAFHSAD

lendInfinue - Monday, September 24, 2012 7:51:04 AM

ERYERSDGSADASDGHASD YUKYSDGSADASDGHASD
GJTRZSDGASDASDGHASD ZVXZADFHGDAFSDGASD
DSGASDGSADADFHGAD SDGSDASDGASDDFHAD
QWERSDGSADGASDFHGAD ZVXZSDGSADASDFHGAD

Amidwayimpalm - Thursday, September 27, 2012 1:08:03 PM

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?

TUser - Wednesday, February 13, 2013 9:35:27 AM

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.

mlfrewiiu - Tuesday, February 19, 2013 4:15:51 AM

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

dolpwdhbp - Saturday, March 2, 2013 12:03:20 PM

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

hbbdrncro - Sunday, March 10, 2013 8:43:57 PM

fghfhfhfhhfhgfhfhfhfhfhfffhfhfhfhfyfyfytftyfyfyftffhfhffgghfhfyfyfyfyfyfyfyftyfy

kennysgnu - Tuesday, March 12, 2013 9:12:02 PM

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.

SawVariavak - Tuesday, March 19, 2013 2:29:43 AM

Broken heart quotes

william hill - Wednesday, April 10, 2013 3:51:26 PM

Little rascal quotes

Extenze prices - Sunday, April 14, 2013 9:43:01 PM

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

qbfbwumua - Thursday, May 9, 2013 1:54:02 AM

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

awgkxeeoo - Friday, May 10, 2013 2:17:11 AM

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.

Dang - Tuesday, May 14, 2013 7:37:22 PM

Comments have been disabled for this content.