In a Trigger can I call a sub-routine?

[kim....@sokiltrucking.com]

 I have a complicated set of IF statements that I would like to separate from the main stream of the trigger.  

[Support]

Yes. Create a Stored Routine (Tools > Stored Routines...)

If the stored routine type is PROCEDURE you can CALL it from your trigger like this:

CALL MyProcedure(param1, param2);

If the stored routine type is FUNCTION you can use  it from your trigger like you can any function:

SELECT MyFunction(param1, param2)

or 

INSERT INTO t1 (a, b) VALUES ( MyFunction(param1, param2), 'dog');

[kim....@sokiltrucking.com]

If I am executing a Trigger and call a Procedure - when the Procedure is finished will my trigger finish executing from where I did the call?  Where should the Procedure reside?

[Support]

Yes, you call if from somewhere in the body of your trigger... execution of the trigger continues if the procedure returns without error... if a SIGNAL is raised in the procedure a rollback happens.

Sample trigger with CALL to a procedure (and also using the new FOR CURSOR LOOP syntax):

BEGIN
 
 DECLARE v_ProductVariantId BIGINT;
 DECLARE v_new_qty BIGINT;
 
 -- subtract from Inbound_Qty and add to
 IF (NEW.Status = 'Completed' AND OLD.Status != 'Completed') THEN
 
   FOR item IN (SELECT Vendor_Item_ID, Qty_Received FROM ROG_Lines WHERE ROG_ID = NEW.ROG_ID) DO
 
     UPDATE Vendor_Items
     SET Inbound_Qty = GREATEST(0, Inbound_Qty - item.Qty_Received)
     WHERE Vendor_Item_ID = item.Vendor_Item_ID;
 
     SELECT Restocks_ProductVariantId INTO v_ProductVariantId
     FROM Vendor_Items
     WHERE Vendor_Item_ID = item.Vendor_Item_ID;
 
     CALL ProductVariants_AdjustQty(v_ProductVariantId, item.Qty_Received);
 
   END FOR;
 END IF;
END

In the above example, ProductVariants_AdjustQty gets called each time through the loop.

[kim....@sokiltrucking.com]

Can you post the Procedure set of coding at goes along with the CALL.