Using triggers for change tracking

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)

Using triggers for change tracking

Author

Topic

gaurish.salunke
Starting Member

7 Posts

Posted - 2009-05-08 : 09:39:34

Hi friends

I am creating a change tracking functionality using triggers that will track the changes made to the db.

Every time a change is made to the db(update,insert,delete)
a track list is added to another table.

I hv managed to do what i wanted,bt the problem here is some of the tables have text datatype and triggers dnt worn on tables which has text as datatype.

Can any one tell me a sol

I am pasting below the trigger i have created

CREATE trigger [dbo].[Contact_ChangeTracking1] on [dbo].[Contact] for insert, update, delete
as

declare
@bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@ContextInfo varbinary (128),
@DBId varchar(5),
@Docname varchar(25)

select @TableName = 'Contact'

select @UpdateDate = convert(varchar(8), getdate(), 104) + ' ' + convert(varchar(12), getdate(), 108)

SET @ContextInfo = CONTEXT_INFO()

Select @DBId = Substring(cast(@ContextInfo AS varchar (50)), 1, PATINDEX('%|%', cast(@ContextInfo AS varchar (50)))-1)

Select @UserName= Substring(cast(@ContextInfo AS varchar (50)), PATINDEX('%|%', cast(@ContextInfo AS varchar (50)))+1, 5)

Select @Docname = reverse(Substring(reverse(cast(@ContextInfo AS varchar (50))), 1, PATINDEX('%|%', reverse(cast(@ContextInfo AS varchar (50))))-1))

if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'

SET @Type = 'U'

select * into #ins from inserted
select * into #del from deleted

set @sql = ''
select @sql = @sql + 'insert into dbo.Audit_Log(unique_id,Docname,Date,time,transaction_type,User)'
select @sql = @sql + ' select ''' + RIGHT(YEAR(GETDATE()),1)+RIGHT(MONTH(GETDATE()),1)+LEFT(NEWID(),8) + ''''
select @sql = @sql + ',''' + @Docname + ''''

select @sql = @sql + ',convert(varchar(10),getdate(),104)'
select @sql = @sql + ',convert(varchar(10),getdate(),108)'
select @sql = @sql + ',''' + @Type + ''''
select @sql = @sql + ',''' + @UserName + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
select @sql = @sql + ' on i.cnt_id= d.cnt_id'

exec (@sql)

Gaurish Salunke
Software Developer
OPSPL

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-05-08 : 10:21:45

quote:
Originally posted by gaurish.salunke

...the problem here is some of the tables have text datatype and triggers dnt worn on tables which has text as datatype.

...say what?

Your code looks functional, but a little verbose.

Run this on any (or all) of the tables you want to track:
http://sqlblindman.pastebin.com/f3b20af45
It generates a script that will create archive tables and triggers for change tracking.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:36:19

if you're using sql 2005, use varchar(max) instead of text,ntext,.. as they're deprecated.

gaurish.salunke
Starting Member

7 Posts

Posted - 2009-05-09 : 01:21:04

hey thanks

well the problem with changing the datatype is that i dnt hv the permission to change it.

Can any one tell me how i can use instead of trigger

I tried wrking on but could not figure out.

Gaurish Salunke
Software Developer
OPSPL

gaurish.salunke
Starting Member

7 Posts

Posted - 2009-05-09 : 01:33:41

hi blindman

thanks for the link.

i tried it out. but the strange thing is that the text datatype is missing from trigger generated.I tried to run it on the tables which contains text field.What it does is eliminates the text field.

Gaurish Salunke
Software Developer
OPSPL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:07:09

what is compatibility level of your db? also what exactly are you trying to do with text field?

gaurish.salunke
Starting Member

7 Posts

Posted - 2009-05-09 : 05:36:57

well d compatibility level is 90

whenever i do some update or insert or delete,log is create in teh audit table with the old and new value.So text id d datatype of one of the field of the contact table.

So what I have done now is I am directly firing thr insert query w/o storing it in @sql.It works bt instead of storing old and new values in the table audit it stores d.colname as old value and i.colname in new value col.

can any help me out.

Gaurish Salunke
Software Developer
OPSPL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources