How do I reference a Text Data Type in a Transact SQL Trigger?

We have a trigger on a table called SJob on a SQL 2005 database, using Transact SQL.  The props field in SJob was changed from Varchar(8000) to Text.  The trigger references the Inserted table.  A Text data type can not be referenced in the Inserted table.  Does anyone have a suggestion to remedy this?
CREATE TRIGGER [dbo].[TRIGGER1] ON [dbo].[SJob] 
FOR UPDATE
AS
 
BEGIN TRAN calcGLAdditions
 
IF UPDATE(state)
BEGIN
	DECLARE
		@co varchar(10),
		@procName varchar(30)
 
	SELECT @co = co
	FROM inserted
 
	SET @procName = 'spsi_' + @co + 'GLAdditions'
 
	IF EXISTS (
		SELECT name
		FROM sysobjects
		WHERE name = @procName AND
			type = 'P')
	BEGIN
 
		DECLARE
			@state varchar(20),
			@jobClass varchar(20),
			@process int
	
		SELECT
			@state = state,
			@jobClass = jobClass
		FROM inserted
	
		IF(@state = 'done' AND @jobClass = 'CalcGL')
		BEGIN
	
			SELECT
				@process = substring(props, charindex('&Process=', props) + 9, 10)
			FROM inserted
	
			EXEC @procName @process;
		END
	END
	ELSE
		PRINT 'No stored procedure: ' + @procName
END
 
COMMIT TRAN

Open in new window

DarinAlredAsked:
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.

pcelbaCommented:
Change the text data type to Varchar(max).
0
dportasCommented:
Firstly, your trigger doesn't work anyway. Triggers need to operate correctly whether zero, 1 or many rows are updated. Yours just picks one random row from the inserted table, which is not good. Never write triggers that way.

You can retrieve the post-updated value of the TEXT column by joining the INSERTED table to the table being updated.

TEXT is deprecated so you really should use VARCHAR(MAX) instead if possible.

You should not use COMMIT in a trigger. It's poor practice to do so because it stops people putting transaction control in other code that updates the table.

Finally, "IF EXISTS (SELECT name FROM sysobjects"  !!!!!!! Do you actually accept developers doing that kind of stuff at runtime? I have never seen anything like it. What is the reason for testing for the existence of the proc and why can't you just create a unit test to verify that the proc works in testing?

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
pcelbaCommented:
If the trigger executes SP of calculated name (data driven approach) then the name check is a must. Unit test does not help here.

If the trigger updates data in some other table then transaction is OK but more obvious is the situation to have a transaction before the UPDATE command executing the trigger.

If you are sure UPDATE affects just one row you don't need to check if more rows are updated.

And the final question: How can be fired UPDATE trigger when no row is updated?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dportasCommented:
>> If you are sure UPDATE affects just one row you don't need to check if more rows are updated.

Triggers should always be written to work properly no matter how many rows are updated. If you do that then you won't need to check if more than one row is affected. If you write a trigger that assumes only one row then it WILL fail eventually because some future developer or DBA will not know or will forget that such a silly limitation exists. Then they will curse you for being so short-sighted. Why put yourself to that trouble?

>> And the final question: How can be fired UPDATE trigger when no row is updated?

Like this for example:
UPDATE tbl SET foo = 1 WHERE 1=0; /* (anything FALSE) */

0
DarinAlredAuthor Commented:
dportas, despite your hostile and acid tone, there is a small pearl in the comment.  I will consider joning the tables and reading the results from there.  We are not in control of this DB structure and must accept certain limitations, good or not.  We are simply trying to augment the functionality of the system with out delving into the object model. of the front end software.  Our trigger calls the named stored procedure which then updeates some other tables with needed data.

Thanks.
0
dportasCommented:
I don't think there was anything hostile in my comments. I pointed out that your trigger doesn't actually work if more than one row is updated and that it's unwise to use COMMIT in a trigger. I would have thought you might find that helpful.
0
pcelbaCommented:
Thank dportas. I've supposed SQL is more clever. I am still learning...

BTW, my goal is robust coding also but if somebody comes with not so bullet proof code I am obviously not complaining until I have to fix it.
0
DarinAlredAuthor Commented:
Thanks for the information guys.  I do appreciate it.  I do not have time to "fix" or "optimize" every piece of code I am maintaining.  So far it looks like dportas' hint is going to help.  when I am ready to do it rightly, I will seek guidance again.  Thanks for the answer.
0
pcelbaCommented:
hostile ... new word for me. Thanks again.

I would say dportas isn't hostile he is just accurate which is necessary and required for programmers.
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
Databases

From novice to tech pro — start learning today.