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
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
robthomas09Author Commented:
Thanks for the link Jim.  The original table cannot be altered so I am stuck working with the text column type.
0
ste5anSenior DeveloperCommented:
They are not allowed:

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.
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi Rob,

Type TEXT has been on the deprecated list for about 10 years, so it appears that you have a very old database.

The right long-term solution is to modernize the database, but I can certainly understand why that isn't on the table.  :)

Write your trigger as an INSERT and and UPDATE, assuming there's a primary key on the original table.

INSERT INTO mytable _shadow (col1, col2, ... coln) SELECT col1, col2, ... coln) FROM mytable;
UPDATE mytable_shadow
  SET shadow.text_col = main.text_col
FROM mytable_shadow shadow
INNER JOIN mytable main
  ON shadow.primary_key = main.primary_key
INNER JOIN INSERTED
  ON inserted.primary_key = main.primary_key

That's not nearly as efficient as you'd want the trigger, but it should get the job done.


Good Luck!
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robthomas09Author Commented:
Thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.