=> Unorthodox DB Updates - Trigger order

From: Rhonda Fischer (anonymous_at_discussions.microsoft.com)
Date: 05/06/04 

Date: Thu, 6 May 2004 09:26:01 -0700

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........ !!!

-------------------------------------------------------------------------------------------------------------------------

=> Operation: Enter Data into AddOrders
     Insert entries into SupplierOrders break numOfPallets into multiples of 26 where >26

    CREATE TRIGGER SplitOrders_insert ON AddOrder
    FOR UPDATE
    AS
    INSERT SupplierOrder (deliveryID, collectionID, collectionDate, numOfPallets)
    SELECT deliveryID, collectionID, orderdate, 26
    FROM inserted
    INNER JOIN Numbers
          ON Numbers.Num <= inserted.numOfPallets
          AND Numbers.Num % 26 = 0
    UNION ALL
    SELECT deliveryID, collectionID, orderdate, NumOfPallets % 26
    FROM inserted
    WHERE numOfPallets % 26 <> 0

--------------------------------------------------------------------------------------------------------------------------
=> Operation: Data into AddOrders activates Trigger that Inserts data into the SupplierOrders table that
     also activates a trigger
     Insert entries into the [Deliveries Made] table where numOfPallets > 22 with unique order number

     CREATE TRIGGER DeliveriesMade_insert ON [SupplierOrder]
     AFTER INSERT, UPDATE
     AS
     INSERT [Deliveries Made] (orderID, [Del Date], deliveryID, collectionID,TotalPallets)
     SELECT [ID], collectionDate, deliveryID, collectionID, numOfPallets
     FROM Inserted
     WHERE numOfPallets > 22

----------------------------------------------------------------------------------------------------------------------------

=> 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