Auditing particular columns in a table

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

General SQL Server Forums

New to SQL Server Programming

Auditing particular columns in a table

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-04-08 : 02:07:38

I have a table with 25 columns. I want to create a trigger so that only 7 colums are audited.

Table_name = Employee
COLUMNS to be audited :
employee_id INT,
employee_last_name NVARCHAR(100),
employee_supervisor_contact_uuid BINARY(16),
last_update_user NVARCHAR(64),
last_update_date INT,
employee_organization_id BINARY(16),
employee_location_id BINARY(16),
employee_status INT

I read some articles and it seems I need to create a table called 'Employee_Audit' which will store all information and then a Trigger 'Employee_trigger'

So that whenever there is an update in any of these 7 columns, it can be stored. Would someone please guide me?
I'm using SQL Server 2005.

Ideally I'd like to audit only few rows (not the whole column)i.e. audit the 7 columns in the particular column where employee_ID is (123,456,789,101,,1012,1015)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-08 : 02:40:40

Go through Columns_update[url]http://msdn.microsoft.com/en-us/library/ms186329(SQL.90).aspx[/url]

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-08 : 03:02:43

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 05:16:12

quote:
Originally posted by senthil_nagore

Go through Columns_update[url]http://msdn.microsoft.com/en-us/library/ms186329(SQL.90).aspx[/url]

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Please note that it checks if column was involved in an update operation rather than if value was actually updated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-04-08 : 05:46:02

Thanks Vikas.. Coz I tried and it didn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 08:26:29

quote:
Originally posted by s_anr

Thanks Vikas.. Coz I tried and it didn't work.

ok...no problem

for checking actual values use INSERTED and DELETED tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources