Does this code make sense? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Does this code make sense?

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-10 : 07:59:14

I found this piece of code in a trigger, written by a vendor. It seems a bit redundant to me, is there any advantage of doing the update this way?

Jim


UPDATE Quote 
SET Quote.LineOfBusiness = (SELECT [Value] 
                            FROM inserted 
                            WHERE [Name]= 'LineOfBusiness' 
                            AND    QuoteID = Quote.QuoteID)
 WHERE EXISTS( SELECT QuoteID FROM inserted WHERE [Name] = 'LineOfBusiness' AND QuoteID = Quote.QuoteID )

Everyday I learn something that somebody else already knew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 08:04:44

the trigger is it on Quote table ?

What type of trigger is this ? AFTER or INSTEAD OF ?

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-10 : 08:11:18

The trigger is FOR INSERT and it's on a different table. The entire code for the trigger is below. I just figured if it was bad for one it's bad for all

Jim


ALTER TRIGGER [UpdateQuoteTable] on [dbo].[UserDefinedData]
    FOR INSERT
    AS
        BEGIN

            UPDATE Quote SET Quote.LineOfBusiness = (SELECT [Value] FROM inserted WHERE [Name] = 'LineOfBusiness' AND QuoteID = Quote.QuoteID)
                WHERE EXISTS( SELECT QuoteID FROM inserted WHERE [Name] = 'LineOfBusiness' AND QuoteID = Quote.QuoteID )

            UPDATE Quote SET Quote.FEIN = (SELECT [Value] FROM inserted WHERE [Name] = 'FEIN' AND QuoteID = Quote.QuoteID)
                WHERE EXISTS( SELECT QuoteID FROM inserted WHERE [Name] = 'FEIN' AND QuoteID = Quote.QuoteID )

            UPDATE Quote SET Quote.AgencyCode = (SELECT [Value] FROM inserted WHERE [Name] = 'AgencyCode' AND QuoteID = Quote.QuoteID)
                WHERE EXISTS( SELECT QuoteID FROM inserted WHERE [Name] = 'AgencyCode' AND QuoteID = Quote.QuoteID )
FROM inserted WHERE [Name] = 'ThisQuoteIsWebRater' AND QuoteID = Quote.QuoteID)
				WHERE EXISTS( SELECT QuoteID FROM inserted WHERE [Name] = 'ThisQuoteIsWebRater' AND QuoteID = Quote.QuoteID )

END

Everyday I learn something that somebody else already knew

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 08:15:28

quote:

Originally posted by jimf

I found this piece of code in a trigger, written by a vendor. It seems a bit redundant to me, is there any advantage of doing the update this way?

Jim


UPDATE Quote 
SET Quote.LineOfBusiness = (SELECT [Value] 
                            FROM inserted 
                            WHERE [Name]= 'LineOfBusiness' 
                            AND    QuoteID = Quote.QuoteID)
 WHERE EXISTS( SELECT QuoteID FROM inserted WHERE [Name] = 'LineOfBusiness' AND QuoteID = Quote.QuoteID )


UPDATE Q
SET Q.LineOfBusiness=I.Value
FROM QUOTE AS Q
INNER JOIN INSERTED AS I 
ON Q.QUOTEID=I.QUOTEID AND I.NAME='LineOfBusiness'

Other way pleaser look for OUTPUT CLAUSE...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 08:15:38

it's bad. Basically the sub-query will break if there are more than 1 row inserted

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 08:21:41

you could change it to something like this ..


UPDATE 	q
SET	LineOfBusiness	= case when i.[Name] = 'LineOfBusiness' then i.[Value] else q.LineOfBusiness end,
	FEIN		= case when i.[Name] = 'FEIN' 		then i.[Value] else q.FEIN end,
	AgencyCode	= case when i.[Name] = 'AgencyCode' 	then i.[Value] else q.AgencyCode end
FROM	Quote q
	INNER JOIN inserted i	on	q.QuoteID	= i.QuoteID
WHERE	i.[Name]	in ('LineOfBusiness', 'FEIN', 'AgencyCode')

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-10 : 08:26:20

Your code makes a lot more sense to me. I can't change the trigger, but will use it as evidence against the vendor. Is the purpose of duplicating the conditions of the where clause in the where exists clause to ensure that only one row gets updated?

Thanks,

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources