Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Audit table trigger performance hit

Author  Topic 

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-13 : 16:12:40
For the first time since I've used SQL Server 7.0 in tandem with my classsic ASP web application, I'm seeing significant performance degradation after adding Nigel Rivett's excellent Audit table trigger (see end of msg for his code).

I want to capture all updates to a specific table, and updates are made via the web app. Normally updates to the db are instantaneous, even with other (admittedly less complex) triggers involved. After adding Nigel's trigger below, the web page hangs for quite a bit of time before posting, on the order of 30 seconds when only minor updates are being recorded. I'm guessing this somewhat complex trigger requires more processing power than I currently possess on my win2k server. Any tips to increase performance?

thanks

==================================================

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/

--Set up the tables
if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit_MAIN_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit_MAIN_TABLE]
go
create table Audit_MAIN_TABLE (Type char(1), TableName varchar(128), PK varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go


create trigger tr_MAIN_TABLE on MAIN_TABLE 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)

select @TableName = 'MAIN_TABLE'

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

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_MAIN_TABLE (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKSelect
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
go




Kristen
Test

22859 Posts

Posted - 2005-07-14 : 00:46:32
Nigel's method is straightforward to impliment, but hefty on resources at run time.

I have only once implimented an audit system which stored table / column / OldData / NewData in a single auditing table and it was a nightmare.

We built scripts for each table's triggers, so it did not have the performance issues of a generic script, but it was still a monster. The scripts were massive, the data generated was massive, the data was poorly indexed, and it was a very difficult to report on the data changes in a way that was useful to the users.

Instead we now use one audit table per user data table. Triggers only store deleted/updated rows (i.e. the current record is NOT in the audit table, that's in the actual user data table). Reporting of changes to a row is easy, data volume is modest, purging of old data is easy (in addition to "Keep 6 months worth" it is also easy to do "Keep 6 months worth and at least the last 10 changes"), indexes etc. can be set appropriately on a table-by-table basis, and performance is excellent.

I suppose for an application with some frequently changing columns, and lots of never changing columns, this may be wasteful of audit table space, but on balance its stood us very well over the years.

Our audit tables have two additional columns at the start - a "Type" and an "Audit Date", and then the triggers look something like this:

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

INSERT dbo.MyAuditTable
SELECT [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
[AuditDate] = GetDate(),
D.*
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.MyPK = D.MyPK
GO


Edit: Removed INSERT trigger type - not relevant to this specific code snippet

Edit2: The latest code for this routine, including detailed discussion and experience gained over the years, is now the Scripts Forum: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215
That link also describes Reporting, Purging stale data, how to only "log" some of the columns in the table, and so on.

Kristen
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-07-14 : 03:08:04
Our audit triggers do exactly the same as Kristen has described (though his code is more slick than mine so I may just have to steal it!)


steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-14 : 09:42:37
I too think You would be better off with 1 audit table per table.
The generic solution is cool, but is nonperformant and complex (except for the setup).

>> "AFTER INSERT, UPDATE, DELETE"

Very slick.
it will mark an updated PK as D, hmmm, oh well, never mind, just a remark, take no notice.

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 11:23:15
We tend to have "other stuff" in our triggers, hence the AFTER INSERT, but it ain't required for this cut-down instance - thanks Rocky.

Not sure what's best with a changed PK. We don't allow that in our applications, generally, so not really a problem for us - I would see that as "AAA was deleted and ZZZ was inserted".

"nonperformant" - I've just got to use that word!!

Kristen
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-14 : 12:39:50
thanks for the good advice. Another quick one, if I may.

When testing Nigel's trigger I, as server/db admin, am able to successfully run a table update and the audit table update w/o problems.

when my users attempt to run it, however, the "no PK" error is raised, as below, after the MAIN_TABLE is updated. The Audit table is of course not updated.

if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)

This appears to be a permissions issue. Do I need to do a dbo.INFORMATION_SCHEMA in the trigger code to allow non-admin users to post to the Audit table w/o errors?

thanks again

Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-14 : 12:41:19
AAA, go to the pub, and ZZZ go to bed.

I tend to separate the triggers into INSERT / UPDATE / DELETE ones if there is more logic than the usual logging / RI.
Actually I don't use them that much, and mostly for RI purposes.

rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-14 : 12:47:20
Good question, steelkit.
INFORMATION_SCHEMA, is the owner
So You already qualified it.
Double double check that You are not missing any PK ?

information_schema doesn't return metadata about unique indexes!, fwi.
only unique constraints and pk.


rockmoose
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 13:24:55
Soeone else posted that they were getting "No PK" errors recently
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51902
- they may NOT have had a PK on the table of course, otherwise there must be some sort of permissions issue.

Presumably the table that the error is complaining about is the same one as you tested?

Log in to Query Analyser AS A USER and see what it says for the various SQL Statements that this trigger is using?

Bit scary if you have to give INSERT permissions on the Audit tables to the users IMHO. (All our table inserts/updates/deleted are via Sprocs, so I don't have to worry about that, our users have Zero direct permissions to tables)

Kristen
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-14 : 13:54:08
Kristen,

thx, yes the table in question is the same one I am able to test successfully. The trigger runs fine when I run it but throws the "no PK" error when my users run it. The table that is being audited definitely has a PK.

this is very odd, I can't see where I need to upgrade permissions. Oh, and I did give full permissions to the audit table just to see if this would clear up the problem, but no go, users still get the "no PK" error.

My inserts/updates etc. also are done via sprocs if that helps.

thx
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 14:09:18
Hmmm ... I can only suggest you try logging into Query Analyser using an "End User" login and see what errors you get. You could then trying running just parts of the SProc to see what returns "NULL" that would coersce that PK error message.

In particular this bit:

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

Kristen
Go to Top of Page

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-14 : 14:19:44
fascinating. when I run that snippet of code with my account in QA, one row is returned with PK info as expected.

When I run it with a test account that is included in the Public role for the database in question, zero rows returned.

** So far, the only way I am able to return a valid row when logged in as a regular user is if I temporarily bump up privileges for the test account to System Administrator role, which is obviously not the solution I am after.

Now, I think I like this approach much better...

http://www.4guysfromrolla.com/webtech/091901-1.shtml
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 16:24:28
I'm getting different amounts of data returned from queries on the INFORMATION_SCHEMA views too - depending who I am logged on as (Guest or Sysadmin).

Looking like permissions of some sort.

I cannot find anything in BoL, and very little [relevant] in Google, but I did turn up this (uncorroborated) piece:

"The information_schema views only show the objects on which the user has
permissions."

which would, of course, make it as a good as useless for this task!

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-14 : 18:16:18
This is in the where clause of the INFORMATION_SCHEMA views:
"permissions(t_obj.id) != 0"
some built in function...

You can see the code in the master database.
Maybe You can roll Your own views, based on the INFORMATION_SCHEMA code.

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-15 : 01:25:17
I think this is a bit worrying as INFORMATION_SCHEMA views are supposed to be replacing use of sysobjects etc. Also a bit worrying that I couldn't find hide nor hair of this in BoL - given that sysobjects etc. is deprecated.

Kristen
Go to Top of Page

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-18 : 08:41:43
quote:
Originally posted by Kristen

Nigel's method is straightforward to impliment, but hefty on resources at run time.

I have only once implimented an audit system which stored table / column / OldData / NewData in a single auditing table and it was a nightmare.

We built scripts for each table's triggers, so it did not have the performance issues of a generic script, but it was still a monster. The scripts were massive, the data generated was massive, the data was poorly indexed, and it was a very difficult to report on the data changes in a way that was useful to the users.

Instead we now use one audit table per user data table. Triggers only store deleted/updated rows (i.e. the current record is NOT in the audit table, that's in the actual user data table). Reporting of changes to a row is easy, data volume is modest, purging of old data is easy (in addition to "Keep 6 months worth" it is also easy to do "Keep 6 months worth and at least the last 10 changes"), indexes etc. can be set appropriately on a table-by-table basis, and performance is excellent.

I suppose for an application with some frequently changing columns, and lots of never changing columns, this may be wasteful of audit table space, but on balance its stood us very well over the years.

Our audit tables have two additional columns at the start - a "Type" and an "Audit Date", and then the triggers look something like this:

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

INSERT dbo.MyAuditTable
SELECT [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
[AuditDate] = GetDate(),
D.*
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.MyPK = D.MyPK
GO


Edit: Removed INSERT trigger type - not relevant to this specific code snippet
Kristen



I'm currently using Nigel's Audit Trail trigger and haven't had any noticeable performance issues so far. In order to reduce the size of the audit trail table, I am only storing the primary key in the case of an insert (which can be easily used to lookup the row in the table) and all the fields that have changed in the case of an update. I don't allow deleting of data.

Adi

-------------------------
/me sux @sql server
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-18 : 10:16:27
regarding the non-admins getting the "no PK" error, I believe the issue is that the user selecting values out of information_schema owned views need to have appropriate permissions to the (subject) objects themselvles. I know that made no sense, try this to see what I'm talking about.


exec master..sp_addlogin 'test', 'test'
go
exec <someDB>..sp_grantDBAccess 'test'
go

--connected as user test and using <someDB>:
select * from information_schema.tables

--connected as admin:
grant select on <someDB>.SomeTable to test
go
--connected as user test and using <someDB>:
select * from information_schema.tables

exec <someDB>..sp_dropuser 'test'
go
exec master..sp_droplogin 'test'


Be One with the Optimizer
TG
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-19 : 07:39:38
Indeed, but that would defeat the object of a trigger being run as a result of an SProc and the trigger then requiring that the connected user needs full access to the underlying table, wouldn't it?

Kristen
Go to Top of Page

lb6688
Starting Member

18 Posts

Posted - 2010-08-15 : 13:58:32
Come cross this excellent post, Kristen, I implemented a audit trigger based on your post and the performance was great (as you said). Now, how do I report "changes" on the columns/row, in your post, you said: " Reporting of changes to a row is easy..." can you elaborate it a little bit?

THANKS !!!
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-09-13 : 17:44:12
Came croos this post, awesome its help alot. I have question, This Trigger track changes on table. How i can trak changes on DATABASE, and how i can track changes on few columns, insted of full table or database? any advise would be big help. Thanks.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 03:47:22
"Reporting of changes to a row is easy..." can you elaborate it a little bit?"

Edit: The latest code for this routine, including detailed discussion and experience gained over the years, is now the Scripts Forum: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215
That link also describes Reporting, Purging stale data, how to only "log" some of the columns in the table, and so on.

Sorry, this was a while ago and I missed the question, but in case of any use here's how we do it.

We have two additional columns on the Audit tables - Action (U=Update, D=Delete; we only store the previous-values, not the current-values as they are in the record itself) and the AuditDate. These are always the first two columns in the Audit Table. The rest of the columns are in the same sequence as the main table.

Our Audit Table column names have a "A_" prefix

SELECT [Act],
[Date],
* -- Or use an explicit column list Col1, Col2, ...
FROM
(
SELECT [Act] = '*',
[Date] = MyRecordUpdateDate, -- or use GetDate() to show "current value"
*
FROM dbo.MyTable
WHERE MyPrimaryKey1 = @MyPrimaryKey1
AND MyPrimaryKey2 = @MyPrimaryKey2
AND MyPrimaryKey3 = @MyPrimaryKey3
...
UNION ALL
SELECT *
FROM
(
SELECT TOP 100 * -- First two columns are Action (Update/Delete) and AuditDate
FROM dbo.Audit_MyTableName
WHERE A_MyPrimaryKey1 = @MyPrimaryKey1
AND A_MyPrimaryKey2 = @MyPrimaryKey2
AND A_MyPrimaryKey3 = @MyPrimaryKey3
...
ORDER BY A_AuditDate DESC
) AS X
) AS X
ORDER BY CASE WHEN [Act] = '*' THEN 1 ELSE 2 END, X.[Date] DESC, X.MyPrimaryKey1, X.MyPrimaryKey2, X.MyPrimaryKey3, ...

"how i can track changes on few columns"

Modification to the code above:

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

INSERT dbo.MyAuditTable
(
Col1, Col2, ...
)

SELECT [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,
[AuditDate] = GetDate(),
D.Col1, D.Col2, ...
FROM deleted D
LEFT OUTER JOIN inserted I
ON I.MyPK = D.MyPK
GO
Go to Top of Page
    Next Page

- Advertisement -