History Table - Insert,Update,Delete

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

History Table - Insert,Update,Delete

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2011-11-30 : 03:51:16

Hello,

I have a following tables:

columns of table temp:
id [PK]
firstName
LastName

Columns of table temp_History:
AuditId [PK]
Id --> [PK] of temp table
firstName
LastName
Audit_Date
Audit_Type

I want to maintain the history of temp table in such a way that when any of the INSERT,UPDATE,DELETE operation happens, a record should be inserted in temp_History table in the following way:

1. when a record is inserted in temp table audit_Type of temp_History table should be 'Insert'
2. when any column is updated in temp table,audit_Type should be 'Update'
3. when a record is deleted in temp table, audit_Type should be 'Delete'

I want to use triggers. I am new to triggers, so a code sample would be helpful

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 06:36:56

see
http://www.nigelrivett.net/#Triggers
It's usual to log the before version and not the after - i.e. no logging for insert and only the before image for deletes.

create trigger xxx on temp
as
declare @type varchar(1) = 'D'
if exists (select * from inserted)
if exists (select * from deleted
select @type = 'U'
else
select @type = 'I'

if @type = 'I'
insert temp_History (id, firstName, lastName, Audit_Date, Audit_Type)
select id, firstName, lastName, getdate(), @type
from inserted

if @type = 'D'
insert temp_History (id, firstName, lastName, Audit_Date, Audit_Type)
select id, firstName, lastName, getdate(), @type
from deleted

if @type = 'U'
insert temp_History (id, firstName, lastName, Audit_Date, Audit_Type)
select t1.id, t1.firstName, t1.lastName, getdate(), @type
from inserted t1
join deleted t2
on t1.id = t2.id
and (t1.firstName <> t2.firstName or (t1.firstName is null and t2.firstName is not null) or (t1.firstName is not null and t2.firstName is null))
and (t1.lastName <> t2.lastName or (t1.lastName is null and t2.lastName is not null) or (t1.lastName is not null and t2.lastName is null))
union all
select t1.id, t1.firstName, t1.lastName, getdate(), @type
from inserted t2
join deleted t1
on t1.id = t2.id
and (t1.firstName <> t2.firstName or (t1.firstName is null and t2.firstName is not null) or (t1.firstName is not null and t2.firstName is null))
and (t1.lastName <> t2.lastName or (t1.lastName is null and t2.lastName is not null) or (t1.lastName is not null and t2.lastName is null))

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