Re: => Unorthodox DB Updates - Trigger order
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 05/06/04
- Next message: Steve Kass: "Re: indexing problem"
- Previous message: marty: "Re: how do I select len(foo) for an ntext field?"
- In reply to: Rhonda Fischer: "=> Unorthodox DB Updates - Trigger order"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 06 May 2004 18:59:47 +0200
On Thu, 6 May 2004 09:26:01 -0700, Rhonda Fischer wrote:
>Hello,
>
>Unfortunately I am attempting to do something a bit weird, in an endeavour to retreive a unique number
>from a table that I've just stuck onto my SupplierOrders table as a kind of join between my database and
>the company non-normalised database. These are the instruction of my manager, very strange.
>
>My first two triggers work successfully, as soon as I attempt to run the third I get the error Msg:
>Maximum stored rocedure, function, trigger or view nesting level exceeded (limit 32) So I tried:
>
>Exec sp_settriggerorder @triggername ='SplitOrders_insert ',
> @order = 'first',
> @stmttype = 'UPDATE'
>
>Exec sp_settriggerorder @triggername = 'SupplierOrder_update',
> @order = 'last',
> @stmttype = 'UPDATE'
>
>without effect........ !!!
(snip)
>----------------------------------------------------------------------------------------------------------------------------
>
>=> Operation: Unique number assigned to new Deliveries Made Entry used to update columns in the
> original SupplierOrder table trigger assigned
>
> CREATE TRIGGER SupplierOrder_update ON [Deliveries Made]
> AFTER INSERT, UPDATE
> AS
> UPDATE [SupplierOrder]
> SET collectionMovementID = inserted.[ID]
> FROM [Deliveries Made] INNER JOIN
> [Deliveries Made] AS inserted ON [Deliveries Made].orderID = inserted.orderID
>
>'---------------------------------------------------------------------------------------------------------------
>
>Yuck, yuck, yuck, it doesn't look good I know. But if you have any suggestions for trigger ordering it
>would be much appreciated otherwise I might just have to fudge it a bit using queries activated from
>a form.... oh dear.
>
>Thank you kindly.
>
>Rhonda
Hi Rhonda,
Yuck, yuck, yuck indeed. I really dislike the use of "inserted" as
alias for a table inside a trigger. In a trigger, I expect "inserted"
to be the pseudo-table with the new rows. I also dislike the fact that
your last trigger updates all rows in the table, not just the ones
that were inserted or updated when the trigger was fired.
However, the error you got is not related to this yuckiness, nor is it
related to the trigger ordering. It's a simple case of endless
recursion. The last trigger is fired on each update of SupplierOrder.
And it does an update to the SupplierOrder table, so it will trigger
itself. Endlessly (or at least 32 times, the maximum recursion level
allowed by SQL Server).
Fix 1 (simple): Use IF NOT UPDATE(collectionMovementID) in the
beginning of the third trigger, so that it'll do nothing if fired by
itself.
Fix 2 (less simple, but better): Make sure you put the correct
collectionMovementID into the SupplierOrder rows in the second trigger
(where these rows are inserted). Preferably in the same statement, in
a second statement if it is really necessary.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Steve Kass: "Re: indexing problem"
- Previous message: marty: "Re: how do I select len(foo) for an ntext field?"
- In reply to: Rhonda Fischer: "=> Unorthodox DB Updates - Trigger order"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
