November 12, 2009 at 7:18 am
I followed one of the examples in sqlservercentral to create a trigger that stores data in an audit table and it was working fine until I added a text field and received the following message:
Server: Msg 311, Level 16, State 1, Procedure tMeet_Master_Log, Line 4
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
My trigger is below. the "Description" field is a text field. Is there any workaround?
alter trigger tMeet_Master_Log on Meet_Master
After insert, update, delete
As
Insert into Meet_Master_Log (ActionTaken, Meeting, Title,Description,LogDate,LogBy)
Select
Case
When inserted.Meeting is null then 'D'
When deleted.Meeting is null then 'I'
Else 'U'
End,
isnull(inserted.Meeting, deleted.Meeting),
isnull(inserted.Title, deleted.Title),
isnull(inserted.Description, deleted.Description),
GetDate(),
SUSER_SNAME()
from inserted
full outer join deleted
on inserted.Meeting = deleted.Meeting
In the above trigger, the "Description" field is a text field. Is there any way I can incorporate this field?
I just realized that the there is also an image field that I haven't even gotten to yet.
Thank you.
Roger
November 12, 2009 at 7:59 am
You can't use trigger-based audits for text, image or other blob data types in SQL 2000.
What you'll have to do is do the auditing from the update commands, whether those are stored procs or something direct from higher layer code. Or rely on a trace to track the commands issued (which doesn't track the data directly).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2009 at 8:46 am
Not sure what you mean. That if I don't have it looking at inserted and deleted and just update, that I can work around the issue?
Thank you.
Roger
November 13, 2009 at 7:02 am
Well, somewhere there's code that's issuing the update command. Whether that's in a proc or in the front end, or the data access layer, or somewhere else, it has to exist somewhere. What you can do is add auditing to that piece of code. What kind of auditing it can do is going to depend on where it exists and how it's written, but it can certainly do something.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2009 at 2:29 am
You may join inserted and deleted tables by primary/unique key to the base table and grab text values from there.
Insert into Meet_Master_Log (ActionTaken, Meeting, Title,Description,LogDate,LogBy)
Select
Case
When inserted.Meeting is null then 'D'
When deleted.Meeting is null then 'I'
Else 'U'
End,
isnull(inserted.Meeting, deleted.Meeting),
isnull(inserted.Title, deleted.Title),
M.Description,
GetDate(),
SUSER_SNAME()
from inserted
full outer join deleted on inserted.Meeting = deleted.Meeting
INNER JOIN dbo.Meet_Master M ON M.Meeting = ISNULL(inserted.Meeting, deleted.Meeting)
_____________
Code for TallyGenerator
November 15, 2009 at 6:44 am
Perfect! Thank you!
Roger
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply