Solved

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

Posted on 2009-07-08
9
438 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2013 tmp files 3 41
How to use three values with DATEDIFF 3 35
Access join syntax when converting to T-SQL query 4 36
Select question from MySQL 1 13
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…

828 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