January 30, 2012 at 2:09 am
I am trying to write a trigger to add to a field I am inserting. I have put hello in for testing purposes (I will actually want this to be a vaule from another view) I am getting the following error. Any ideas? Glad of any help.
Msg 311, Level 16, State 1, Procedure udef_update_order_notes, Line 9
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
UPDATE dbo.ORD_HEADER
SET OH_INTERNL_NOTE = inserted.OH_INTERNL_NOTE + 'HELLO'
FROM ord_header INNER JOIN inserted ON ord_header.OH_PRIMARY = inserted.OH_PRIMARY
January 30, 2012 at 2:18 am
As per BOL.
SQL Server 2008 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables.
January 30, 2012 at 2:20 am
This link explains your issue... http://social.msdn.microsoft.com/Forums/en/transactsql/thread/bac1f56f-9a59-4ca6-b750-80b3f28a1f5f
January 30, 2012 at 2:22 am
Thanks
January 30, 2012 at 2:25 am
AFTER triggers do not support text, ntext or image columns.
If you need to work with these datatypes, you could try with INSTEAD OF triggers.
Refer to http://msdn.microsoft.com/en-us/library/ms175196.aspx for more information
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply