We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
487 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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
Change the text data type to Varchar(max).
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
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?

Commented:
>> 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) */

Author

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.

Commented:
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.
CERTIFIED EXPERT

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

Author

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.
CERTIFIED EXPERT

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.