August 13, 2003 at 6:00 am
I tried to create a trigger with this SQL statement:
========================================
create trigger del_main_tri on T_MAIN for delete
as
insert main_audit
select
*
, suser_sname() as modifiedby
, getdate() as modifiedon
, 'D' as [action]
from deleted d
return
=============================================
This error was returned:
--------------------------------------------
Server: Msg 311, Level 16, State 1, Procedure del_main_tri, Line 5
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Server: Msg 311, Level 16, State 1, Procedure del_main_tri, Line 5
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
--------------------------------------------
There is a text column in the T_MAIN table.
Does anyone has any suggestion how I can achieve the same objective without this error.
Is INSTEAD OF trigger an alternative to resolve this problem.
Thanks.
August 13, 2003 at 6:13 am
Instead of is your best bet.
Andy
August 13, 2003 at 6:17 am
Refer to BOL
It says that
If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.
(Please tell me how to set the compatibilty level 🙁
)
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 13, 2003 at 9:29 am
Does using instead of trigger mean I have to handle the insert and delete in my script after saving the record to my audit table?
August 14, 2003 at 3:55 am
BrokenRulz,
The compatibility level of 80 = SQL 2K. To check this look at the properties of any/all of your databases on the Options tab.
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply