Adding simple trigger-based auditing to your SQL Server database

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

weliaccoula — Tue, 22 Dec 2009 23:44:25 GMT

Hi http://lenzaripa.gu

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

Simbu — Fri, 18 Dec 2009 12:15:27 GMT

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

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

Yassine Rabbouh — Wed, 16 Dec 2009 11:56:45 GMT

C'est trés interessant!

Merci beaucoup et bonne continuation.

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

Urbacharl — Sat, 05 Dec 2009 10:35:34 GMT

Alohi. Mi zer novazo. http://jestormani.net

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

Jose — Thu, 29 Oct 2009 22:02:09 GMT

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.

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

Dan — Thu, 24 Sep 2009 17:22:53 GMT

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?

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

Matt — Thu, 03 Sep 2009 15:05:17 GMT

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

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

JoeS — Mon, 24 Aug 2009 20:26:29 GMT

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

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

Sean — Mon, 27 Jul 2009 19:30:18 GMT

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.

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

wicks — Fri, 26 Jun 2009 10:55:16 GMT

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.