Triggers to capture audit trials

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

Transact-SQL (2005)

Triggers to capture audit trials

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-01 : 00:45:48

Dear All,

How can i create trigger on the table with the following information to capture

The Audit trail shall record:

User name
Object changed(table name to which insertion/delete happen)
Date and time (h/min/sec),
Indication of record creation, modification or deletion,
In case of modification or deletion: old value,

Any help in this regard.

Thanks,
Gangadhar

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-01 : 01:46:39

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create TRIGGER [tgr_name] ON [dbo].[table_name]
FOR INSERT, UPDATE, DELETE
AS
Begin

declare @inserted bit, @deleted bit

Declare @transaction varchar(20)

set @inserted = case when exists(select top 1 1 from inserted) then 1 else 0 end
set @deleted= case when exists(select top 1 1 from deleted) then 1 else 0 end

if @inserted = 1 and @deleted = 1 --update
begin

set @transaction='modified'

end

if @inserted = 0 and @deleted = 1 --delete
begin

set @transaction='deleted'

end

if @inserted = 1 and @deleted = 0 --insert
begin

set @transaction='created'

end

if (@transaction='created' or @transaction='modified' )
Begin

insert into hist_table(col1,col2,col3,....,transaction,modified_date)

Select col1,col2,col3,....,@transaction,getdate() from inserted

End
else
Begin

insert into hist_table(col1,col2,col3,....,transaction,modified_date)

Select col1,col2,col3,....,@transaction,getdate() from Deleted

end

END

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

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-09-01 : 08:36:56

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources