Two Triggers on Same Table - Error

[RJL1]

I have 2 triggers on the SHIPPING_CONTAINER table when I run a packing functions I get an error

Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)

If I turn either trigger off the other works is whne I have both enabled the error is returned. I not to experienced in Triggers so I may be missign something obvious

Trigger 1 Moves the Tare weight from Truck table to a user defined fiedn in the shipping container for company 318 only

BEGIN
UPDATE SHIPPING_CONTAINER SET
SHIPPING_CONTAINER.USER_DEF1 = T.EMPTY_WEIGHT,
SHIPPING_CONTAINER.CONTAINER_COUNT_NUMBER =
(SELECT COUNT(CONTAINER_TYPE) FROM SHIPPING_CONTAINER SC
WHERE I.INTERNAL_SHIPMENT_NUM = SC.INTERNAL_SHIPMENT_NUM AND
SC.CONTAINER_TYPE = 'Trailer')
FROM  INSERTED I
INNER JOIN SHIPPING_CONTAINER
ON I.INTERNAL_CONTAINER_NUM = SHIPPING_CONTAINER.INTERNAL_CONTAINER_NUM
INNER JOIN WEPK_TRANSLOAD_TRUCK T
ON I.CONTAINER_ID = T.TRUCK_NUM
WHERE
SHIPPING_CONTAINER.COMPANY = '318'
AND ISNULL(SHIPPING_CONTAINER.STATUS,0) > 650
AND I.INTERNAL_CONTAINER_NUM = SHIPPING_CONTAINER.INTERNAL_CONTAINER_NUM
END

Trigger 2 - adds a C to the end of the container ID

BEGIN
UPDATE SHIPPING_CONTAINER
SET SHIPPING_CONTAINER.CONTAINER_ID = SHIPPING_CONTAINER.CONTAINER_ID + 'C'
FROM INSERTED I
WHERE 
SHIPPING_CONTAINER.COMPANY <> '278'
AND ISNULL(SHIPPING_CONTAINER.STATUS,0) > 650
AND I.INTERNAL_CONTAINER_NUM = SHIPPING_CONTAINER.INTERNAL_CONTAINER_NUM
AND RIGHT(SHIPPING_CONTAINER.CONTAINER_ID,1) != 'C'
AND (SELECT LEADING_STS FROM SHIPMENT_HEADER
WHERE SHIPMENT_HEADER.INTERNAL_SHIPMENT_NUM =
I.INTERNAL_SHIPMENT_NUM) > 300
END

Any help with this is appreciated
Thanks
RJL

 

[markros]

I suggest to combine logic of these two triggers into one trigger's procedure. Having multiple triggers on one table usually leads to nothing but trouble.

PluralSight Learning Library

 

[SQLScholar]

What type of trigger is it? Update?

If so surely each stored proc will call the other? Only thing i can think of.

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------