sql 2000 track changes to notes field

[Oliver1]

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.

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.

[dee-u]

Will you not just ALTER the trigger?

ALTER TRIGGER dbo.arcnotes
ON dbo.contact1
INSTEAD OF update

[Oliver1]

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.