Solved

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

Posted on 2009-07-08
9
409 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:DarinAlred
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24807038
Change the text data type to Varchar(max).
0
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
ID: 24807199
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24807398
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
 
LVL 22

Expert Comment

by:dportas
ID: 24807655
>> 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DarinAlred
ID: 24807715
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
 
LVL 22

Expert Comment

by:dportas
ID: 24807797
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24807806
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
 

Author Comment

by:DarinAlred
ID: 24807845
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24807867
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now