sql 2000 track changes to notes field
Hi All,
I'm just trying to write a simple trigger to track the changes made to a field. Now irritatingly the notes field is a text datatype.
So when I try and run the below on my dev box I get
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Code:
CREATE TRIGGER dbo.arcnotes
ON dbo.contact1
for update
AS
IF UPDATE (notes)
Declare @beforen varchar(5000)
Set @beforen = (Select cast(notes as varchar(5000)) From deleted)
Declare @aftern varchar(50)
Set @aftern = (Select cast(notes as varchar(5000)) From Inserted)
insert into narchive (changedate,cuser,notesbefore,notesafter)
values (getdate(),SYSTEM_USER, @beforen, @aftern)
Now looking on google I could use the INSTEAD OF trigger but this concerns me as I guiessing I have to then recreate the sql that triggered the trigger.
Any thoughts on the best way to achive the above.
Thanks.
Re: sql 2000 track changes to notes field
Will you not just ALTER the trigger?
Code:
ALTER TRIGGER dbo.arcnotes
ON dbo.contact1
INSTEAD OF update
Re: sql 2000 track changes to notes field
Doesn't an instead of trigger run instead of the sql that initially triggered the trigger in the first place. So if I just changed my above trigger to an instead of trigger then no updates at all would happen on the notes field, as I would be running the sql in my trigger instead.
Or am I confused.