Trigger on a text field
-
I am trying to audit a table that an end user will be updating a text field. What would be the best way to do this? I created a trigger but when I tried comparing the inserted/deleted text column, I received the following error. I am using SQL2000. From what I read regarding compatability levels of 80 - higher, I thought this would work.
Server: Msg 311, Level 16, State 1, Procedure tU_memo, Line 24
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
-
Well that's just it, those type of columns are not included in the inserted and deleted tables because they can be way too big.
-
So am I out of luck on this?
-
Not really... what do you need to do exactly?
-
basically its a text field that a user can add/remove comments from the front end. what management wants is the ability to track all of those changes(before/after)
-
I think you'll have to track those changes in the sp before firing the main update statement. I don't see any other way of doing that.
-
You need to implement an INSTEAD OF trigger to handle those fields. They (text, image, ntext fields) are available for that kind of trigger!!
Cheers!
* Noel -
You need to implement an INSTEAD OF trigger to handle those fields. They (text, image, ntext fields) are available for that kind of trigger!!
Cheers!
* Noel -
i was able to parse my trigger but i still don't receive my expected results.
i thought this would have been pretty basic but its turning into a huge headache.
the update to the record works but most importantly, nothing gets populated in my insert for audit records below.
CREATE TRIGGER tU_memo ON dbo.memo INSTEAD OF UPDATE AS
-- Compare the inserted colums to the deleted cols to check for differences
select INS.*
from inserted INS inner join deleted DEL
on INS.memoid = DEL.memoid
where INS.memoid = DEL.memoid
AND
(
DEL.description NOT LIKE INS.description
OR
DEL.message NOT LIKE INS.message
 
-- memotype define for provider from front end
and
INS.memotype = 'smemo'
-- Write records to audit tables
if @@ROWCOUNT = 1
BEGIN
INSERT INTO nbf..memo_audit(memoid,time_of_change, changetype,message,description, createid,createdate,
lastupdate, updateid)
SELECT memo.memoid,GETDATE(),'CHANGE BEFORE',memo.message, memo.description, memo.createid,memo.createdate,
memo.lastupdate, memo.updateid FROM deleted as memo
INSERT INTO nbf..memo_audit(memoid,time_of_change,
changetype,message,description, createid,createdate, lastupdate, updateid)
SELECT memo.memoid, GETDATE(),'CHANGE AFTER',memo.message, memo.description,memo.createid,
memo.createdate, memo.lastupdate, memo.updateid FROM memo, deleted where memo.memoid=deleted.memoid
END
-
I have the same problem,
The instead of trigger will populate the audit table but will not update the main table.
What is the solution to that???????
-
You have to manually code the insert for the audit AND the main table. The instead option actually "cancels" the insert.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply