Solved

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

Posted on 2009-07-08
9
456 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 42

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 42

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 

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 42

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 42

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Read about achieving the basic levels of HRIS security in the workplace.
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…

739 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