September 25, 2009 at 6:15 am
Hi,
how can i create an audit trigger to capture all changes (insert,update,delete)in a table columns with the old value and the new value that changed.
i know how to setup this kind of trigger for a specific column on a table but not for all columns together.
THX
September 25, 2009 at 6:45 am
Evendata() can be used to audit events in database level.Assuming that you are using SQL Server 2005 then please refer below link
http://msdn.microsoft.com/en-us/library/ms173781(SQL.90).aspx
September 25, 2009 at 7:08 am
Please go through the below link as well
Don't know if it's of any useful for you. I have implemented in my DB.
September 25, 2009 at 7:49 am
San-847017 (9/25/2009)
Please go through the below link as wellDon't know if it's of any useful for you. I have implemented in my DB.
Nice article, only of use if you have CLR enabled though
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 28, 2009 at 4:37 am
I've a thought about this trigger issue and i need your help here with this test that i try.
i create 2 table
- one the table that needs audit
create table one (id int,fname varchar (50))
- two the table that will hold the columnname
create table holdcolumns (tablename varchar (50),columnname varchar (50))
insert the two columns into the holdcolumns table with the tablenme,and then create the trigger.
my problem is that the @columnname is not get the value from the INSERTED, instead it just put the columnname there.
how can i fix that?
THX
--trigger
create trigger test_trig on one for insert
as
begin
declare @columnname as varchar(100)
declare @tablename as varchar(100)
set @tablename = 'one'
DECLARE CRS_Insert_Trigger CURSOR LOCAL FAST_FORWARD
FOR
select columnname from holdcolumns
where tablename = @tablename
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @columnname
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert into AUDITCHANGESTABLE (pdt,
--tblname,
--clmname,
--usrname,
--event,
--newvalue,
--oldvalue,
--appname)
select getdate(),@tablename,@columnname,system_user,'INSERT',@columnname,NULL,program_name() from inserted
FETCH NEXT FROM CRS_Insert_Trigger INTO @columnname
END
CLOSE CRS_Insert_Trigger
DEALLOCATE CRS_Insert_Trigger
end
September 28, 2009 at 9:25 pm
September 29, 2009 at 2:16 am
is there a way to set the column that updated into variable.
like
declare @columnname as varchar (50)
declare @columnname1 as varchar (50)
set @columnname = 'col1'
set @columnname1 = (select @columnname from inserted)
print @columnname1
--to show the updated value
THX
September 29, 2009 at 2:29 pm
Experts please comment.
MJ
October 1, 2009 at 3:01 pm
can anyone help plz?
is there a way to set the column that updated into variable.
like
declare @columnname as varchar (50)
declare @results as varchar (50)
set @columnname = 'col1'
set @results = (select @columnname from inserted)
print @results
--show the updated value
THX
October 2, 2009 at 4:17 pm
Hope that helps! I've used that in several applications. However, I've often customised it to be able to select the columns I want to audit because sometimes auditing all columns in all tables does not work very well - performancewise.
Being able to correctly determine WHO actually made the changes depends on a number of things and you do in fact have several options. If you don't have the luxury of being able to make many structural changes to your database... then you may want to consider passing additional information to the database as well, you can "hack" a method which involves passing a "Workstation ID" parameter in your connection string. This value can be retrieved via the HOST_NAME() database function. There are several ways to handle passing additional data, but this is a quick and dirty method which I used in a large legacy application where manking too many changes to the database would bring my life to a halt! 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply