robthomas09
asked on
Converting text type column within after insert table trigger
Hello experts,
I have a table below that mimics the design of my original table (limited rows of course). What I am trying to accomplish is this: I have a created a duplicate table with column C in this new table being a varchar (max) type. On the original table I have created a simple after insert table trigger that writes to the new duplicate table (this has to happen for multiple reasons and cannot be avoided). If I include column C from the original table within the table trigger I get: "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables." message. I have a work around in place by leveraging a table trigger on the new duplicate tables to reach back grab the text column but I have been asked to limit the amount of table triggers. So what I need is a way to include column C within the table trigger. Thanks in advance for your help!!
Column: Type
column A varchar
column B varchar
column C Text
I have a table below that mimics the design of my original table (limited rows of course). What I am trying to accomplish is this: I have a created a duplicate table with column C in this new table being a varchar (max) type. On the original table I have created a simple after insert table trigger that writes to the new duplicate table (this has to happen for multiple reasons and cannot be avoided). If I include column C from the original table within the table trigger I get: "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables." message. I have a work around in place by leveraging a table trigger on the new duplicate tables to reach back grab the text column but I have been asked to limit the amount of table triggers. So what I need is a way to include column C within the table trigger. Thanks in advance for your help!!
Column: Type
column A varchar
column B varchar
column C Text
Jim Horn
Last I remember text is being depricated, although as of 2014 that hasn't happened yet, and this may be part of that, and the article suggest using varchar(max) instead.
ASKER
Thanks for the link Jim. The original table cannot be altered so I am stuck working with the text column type.
They are not allowed:
See Using the inserted and deleted Tables.
So without testing, you need an INSTEAD OF trigger.
Even when there are such reasons, maybe the new requirement collides with this. Then you need either change your assumptions or the new requirement.
SQL Server 2008 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers.
See Using the inserted and deleted Tables.
So without testing, you need an INSTEAD OF trigger.
this has to happen for multiple reasons and cannot be avoided.
Even when there are such reasons, maybe the new requirement collides with this. Then you need either change your assumptions or the new requirement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!!