SQL Server Performance Forum – Threads Archive

Field Tracking in Trigger

We are doing field level auditing on company table. If the field value is updated then we are inserting new row into CompanyTrack table with their old values. For this we are using COLUMNS_UPDATED() function inside the trigger and it is working for all the fields except TEXT type field.
The below code is used for the field ‘BDescription’ and type of that field is TEXT. For TEXT type field we are not storing the actual old content instead of we are storing ‘Y’ if it is updated. The below if condition is not working and not inserting the records in table even though it is updated. It is not throwing error as well.
What is wrong? IF @FieldName= ‘BDescription’ –Instead of storing the old value store ‘Y’ if updated.
Begin
Insert Into CompanyTrack (ArchiveID, FieldName, FieldValueOld, UserID)
Select @ArchiveID, ‘BDescription’, ‘Y’, i.UPDATEUSER
From inserted i, deleted d
Where i.CompanyID = d.CompanyID
and Cast(d.BDescription,varchar(8000)) <> Cast(i.BDescription,varchar(8000))
End
Surendra Kalekar

---

BOL, on Using the inserted and deleted Tables: "SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER." Try the UPDATE() function, and also add the underlying table to your FROM clause: If UPDATE(BDescription)
begin
Insert Into CompanyTrack (ArchiveID, FieldName, FieldValueOld, FieldValueNew, UserID)
Select @ArchiveID, ‘BDescription’, ‘ < Old BDescription cannot be logged > ‘, c.bdescription, i.UPDATEUSER
From inserted i, deleted d, company c
Where i.CompanyID = d.CompanyID and i.companyid = c.companyid
end

---

Hi Adriaan,
It is inserting record into table eventhough it is not updated. IF @FieldName = ‘BDescription’
Begin
If UPDATE(BDescription)
Begin
Insert Into CompanyTrack (ArchiveID, FieldName, FieldValueOld, UserID)
Select @ArchiveID, ‘BDescription’, ‘Y’, i.UPDATEUSER
From inserted i, deleted d , company c
Where i.CompanyID = d.CompanyID and i.companyid = c.companyid
–and Cast(d.BDescription,varchar(8000)) <> Cast(i.BDescription,varchar(8000))
End
End Surendra Kalekar

---

Hm that is weird …
create table dbo.test (id INT, txt TEXT)
go
create trigger tr_updt on dbo.test
for update
as
if update(txt)
print ‘txt updated!’
else
print ‘txt not updated!’
go
set nocount on
insert into dbo.test values (1, ‘abcd’)
update dbo.test set id = 2
update dbo.test set txt = ‘abcd’
update dbo.test set txt = ‘bcd’
drop table dbo.test
Results: txt not updated!
txt updated!
txt updated!

---

Oh..Oh… [^] ..this is working.
but in our case our update sp updates almost all the fields, which includes BDescription field as well… and that is why it is inserting the record even though it is not updated.
So is there any trick to compare like…
and Cast(d.BDescription,varchar(8000)) <> Cast(i.BDescription,varchar(8000))
There are very few records where BDescrition is crossing the 8000 character limit.
Surendra Kalekar

---

Hm, the trick would have to be outside of the trigger … well, as long as you’re doing this with an sp: Create a temp table in your sp, where you insert the before and after values. Then apply the update, and if @@ERROR = 0 you copy from the temp table into your tracking table. As long as there is no direct manipulation of data on the table, only through the SP, you should be safe.

---

because of this….
" SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER. "
Right now we have decided not to track the TEXT fields through triggers.
At this stage we can’t change the logic… so we will wait for some good idea.. or will track those TEXT fields by using front-end & sp.
Thanks for your help.
Surendra Kalekar

---

]]>