Trigger To Update Inserted Record w/ Data from another table

[RJL1]

I have a a need to capture some data from one table into the table the receord gets inserted into. Let me explain

I have a SHIPPING_CONTAINER table which has a CONTAINER_ID field and A USER_DEF1 field. The container table also has a numeric status.

Now I also have a table TRUCK which holds truck data one piece is EMPTY_TRUCK_WEIGHT and TRUCK_NUM.

The 2 table match on TRUCK_NUM = SHIPPING_CONTAINER

When the Shipping Container status = 700 I want to grab the EMPTY_TRUCK_WEIGHT from the Truck table and insert it into the Shipping Container USER_DEF1 field

Here is what I have but does not seem to be working

UPDATE SHIPPING_CONTAINER SET
SHIPPING_CONTAINER.USER_DEF1 = 
(SELECT T.EMPTY_WEIGHT FROM TRUCK T
WHERE I.CONTAINER_ID = T.TRUCK_NUM)
FROM INSERTED I
SHIPPING_CONTAINER.STATUS = 700

Any help on this is appreciated as I'm very new to triggers

Thanks
RJL

 

[gmmastros]

You forgot your WHERE keyword.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Sorry I do have the WHERE just missed it when I copy it

UPDATE SHIPPING_CONTAINER SET
SHIPPING_CONTAINER.USER_DEF1 = 
(SELECT T.EMPTY_WEIGHT FROM TRUCK TWHERE I.CONTAINER_ID = T.TRUCK_NUM)
FROM INSERTED I
WHERE
SHIPPING_CONTAINER.STATUS = 700

Does the rest looks correct?

Thanks

 

[gmmastros]

I assume this is a trigger for the SHIPPING_CONTAINER table. Is this correct? Also... do you have a primary key for this table?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think you'll want something like this:

UPDATE SHIPPING_CONTAINER 
SET    SHIPPING_CONTAINER.USER_DEF1 = T.EMPTY_WEIGHT
FROM   INSERTED I 
       INNER JOIN SHIPPING_CONTAINER
         On I.[!]PrimaryKeyColumn[/!] = SHIPPING_CONTAINER.[!]PrimaryKeyColumn[/!]
       INNER JOIN TRUCK T
         ON I.CONTAINER_ID = T.TRUCK_NUM
WHERE  SHIPPING_CONTAINER.STATUS = 700

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

That looks much better but I still get an error. I think is our system not liking something on the update. I see another trigger on the same table but not sure if that is intefering. I will do some more reaserch on this

Thanks

Current Code

ALTER TRIGGER [dbo].[TRANSLOAD_TARE_WEIGHT] ON [dbo].[SHIPPING_CONTAINER]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN
UPDATE SHIPPING_CONTAINER SET
SHIPPING_CONTAINER.USER_DEF7 = T.EMPTY_WEIGHT
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.STATUS = 700
AND I.INTERNAL_CONTAINER_NUM = SHIPPING_CONTAINER.INTERNAL_CONTAINER_NUM
END

 

[RJL1]

I disab le the other trigger and it worked like a charm. I'm not sure how they interact with each other so a little more reaserch may take me all the way (so close)

Thanks

 

[gmmastros]

What is the exact text of the error message?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Error Message

System.Data.SqlClient.SqlException: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Server stack trace: 
   at com.pronto.wrappers.outex.ShippingContainerBasics.PackNewShippingContainer(String stSV, String stContainer, Session sessionCS, PackingDS packDS)
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]: 
   at com.pronto.wrappers.outex.PackingHandling.packContainer(String sessString, String shipContString, Int32 operation, String packPrefsString, String packDSString, Session sessionCS)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [1]: 
   at com.pronto.bl.outex.ShippingContainerPackingRequests.reqPackContainer(String SV, Int32 iCallType, ShippingContainerDO contDO, String stPackPref, String stPackingDS, Int32 iOperation, Double dTotalQty, Session sessionCS)
   at Manh.WMW.Shipping.UI.PackingUI.packContainer(ShippingContainerDO shipContainerDO, Boolean bNewCont, Double dTotalQty

This is the other trigger on that table

BEGIN 
UPDATE shipping_container 
SET shipping_container.container_id = shipping_container.container_id + 'C'
FROM inserted i
WHERE shipping_container.company <> '318'
AND shipping_container.company <> '278' -- Zurn HD company number
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

 

[RJL1]

I chnaged the new trigger to on insert so that fires when the record is inserted and the existing trigger fires when the record is updated. No errors and both triggers worked OK

Thanks

 

[gmmastros]

Ah... I see.

You had 2 triggers each updating the same table, so trigger 1 woudl fire trigger 2, which would fire trigger 1 again. Round and Round you go.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom