Set Based Triggers

Question

Set Based Triggers

rocware

SSC Veteran

Points: 224
More actions
April 13, 2003 at 11:10 pm

#80062

Hi Guys,
I am a newbie to SQLServer and I have a question about set-based triggers.
I am trying to write all my triggers so they are set based, like the following:
UPDATE [ORDER]
SET TONNAGE_QTY = (SELECT i.ORDER_QTY * p.TONNAGE_PER_ITEM
FROM PRODUCT p,
inserted i,
[ORDER] o
WHERE p.PRODUCT_ID = i.PRODUCT_ID AND
o.ORDER_ID = i.ORDER_ID),
UPDATED_BY = @User,
UPDATED_DATE = @Date
WHERE [ORDER].ORDER_ID IN (SELECT ORDER_ID FROM inserted)
This insert trigger updates a column in the ORDER table to a value derived from another ORDER table column multiplied a value in the product table.
I was just wondering if I have the right idea. Also I want to default another colum (TONNAGE_BALANCE) in the ORDER Table to the same calculated value as TONNAGE_QTY. Do I need to perform the calculation again ie: duplicate the assignment select or is there a cooler way to do it.
I remember reading somewhere something about not using cursrors in SQLServer cause they are slow, but assigment selects in set based triggers makes it kinda tricky cause I can't assign to a local variable to reuse and bloats code somewhat. Unless I'm off my tree.
Thanks in advance.
Rich O'Connor

Rich O'Connor
April 16, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Yes, triggers are set based. Ideally you write your code to do what you need in a single set based operation, but there are times when you need to process each row singly (a good example is when you need to execute logic in a stored proc that needs params passed) and when you hit those, a cursor is the right way to do it.

Using triggers to maintain denormalized values is ok (better than relying on the application to do it), if it's just a simple calculation you might find it easier/cleaner to go with a computed column.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

rocware SSC Veteran Points: 224 More actions · Answer 2

Thanks Andy,

for a few days there I thought I might have to change the topic subject to FREE BEER!!! to get a response. 😎

As I said originally I had read that SQL Server Triggers are inefficient but have not found a decent explanation as to why.

I have actually implemented a cursor for this trigger cause I have to fire a generic SP to maintain running product totals etc. So I call the sp in both the Insert and Update Triggers.

The extra assign select was sposed to be used for the seeting of the Order Balance column as a default in the insert. But this has since changed with the cursor implementation as I can localize the return from the select and use the local twice.

How sophisticated can you get with default column values, can you call an userdefined sp or a function in a column "default" expression?

Once Again thanks for the reply (MS SQL Server is a little bit of a paradigm shift from Oracle but I am enjoying it thus far)

Rich O'Connor

crosspatch Hall of Fame Points: 3108 More actions · Answer 3

Hi Rocware. Yup ORACLE To SQLKSWERVER has some interesting Gotcha's, triggers being one of them. More of a general answer than specific to your problem here are some pointers I've found useful.

Firstly I've found if you call stored procedures from your trigger rather than massed inline code you tend to have a more maintainable and flexible solution.

Make use of the Calculated Column facility in SS for automatatically updating the value of columns like totals etc.

Although you don't have the nice ORACLE feature of determining which action is firing the trigger (INSERT UPDATE DELETE) you can roll your own by testing for row-counts in the Inserted And Deleted virtual tables.

Be aware that an SS trigger only fires once for a batch of actions (no FOR EACH ROW). To cater for this I have made use of a cursor(wash my mouth out with soap) of the Virtual table/s to ensure I process all entries in a batch, but you must be sure that you're really only going to deal with a relatively small batch each time.

I've never encountered the equivilant of the 'mutating table' issue

Hope that helps a little

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

rocware SSC Veteran Points: 224 More actions · Answer 4

Hey Crosspatch,

thanks for the reply.

>>Firstly I've found if you call stored procedures from your trigger rather than massed inline code you tend to have a more maintainable and flexible solution.

I agree, I am all for reducing the amount of code I have to punch.

>>Make use of the Calculated Column facility in SS for automatatically updating the value of columns like totals etc.

Hmmm I'll check it out how funcky can you get with code? with calcColumns?

>>cursor(wash my mouth out with soap)

this seems to be a common SS theme. I am only in development with my first SS Database so I don't have huge volumetrics to do performance stats on but I haven't come accross any glaring performance hits with my first (inserted virtual table) based cursor(There I said it, good thing I have coffee flavoured soap).

Is there any definitive text on why cursors cannot be employed to prevent earthquakes?? Or does the government consider them to weapons of mass obstruction? and therefore should embargoed.

Thanks again for your help.

Rich O'Connor

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 5

I agree with Andy.

I think the cursor issues become relevant with upscaling/concurrency issues, specially when the box start to take a bit of strain!

PITIYAR SSC Enthusiast Points: 198 More actions · Answer 6

HI,

Yes using cursors will be a weapon of mass destruction if you use everywhere. It is technique you should use as a last resort. They are enefficient and create more probs specially if use them in triggers