audit trigger - SQL Server Forums

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

audit trigger

Author

Topic

lynk666
Starting Member

2 Posts

Posted - 2007-06-05 : 08:58:49

hi, im kinda new to sql and was just wondering if someone could help.

i need to create a trigger that saves all changes from a table(table1) to an audit table.

-table1-
item_code
item_price
item_description

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-05 : 09:04:14

-- create audit table
select *, update = getdate(), Action = 'U' into table1_au from table1 where 1 = 0

create trigger tr_table1_au on Table1 from update, delete
as
declare @action char(1)

if exists (select * from inserted)
select @action = 'U'
else
select @action = 'D'

insert table1_au
select *, getdate(), @action
from deleted
go

(should name the colunms rather than select * but I'm lazy.

Have a look at triggers on www.nigelrivett.com

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 09:55:19

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Trigger,Triggers,audit%20changes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources