I have a procedure that generates customer specific labels for shipments. We implemented a change where the procedure won't run until the shipment status = S.
I also have an after update trigger on our shipment table to see if the status has changed from R to S and if it has and it's a parcel shipment, to call that procedure automatically. My problem is when I call the procedure from the trigger and select the status of that particular shipment in the procedure, it is still R. How do I get around this?
I did - I updated the table with an update statement in a SQL window (Oracle SQL Developer) then hit commit. In the procedure I select the status and then insert it into an error table and it's the old status.
Unfortunately none of us are set up to use SQL Plus. We use TOAD or SQL Developer exclusively. This company has third party software that we work in mostly and it uses SQL and PL/SQL. it just so happens that we created procedures and functions within a package to facilitate certain processes.
Here's the sequence of events.
A procedure in the package checks if the status of a particular shipment in the shipment table is S with a simple if statement. If it is, the remainder of the code runs and if it isn't a message is inserted into an error table and it drops out. Before this if statement I placed an insert statement into the error table with the current status.
Code snippet of procedure:
gv_ship_id := av_shipment_id;
WHERE polcod = 'UC_INSTANCE_CONSTANTS'
AND polvar = 'AIAG_LABELS'
AND polval = 'GENERATE_LABEL_STATUS';
WHERE ship_id = av_shipment_id;
IF INSTR (lv_valid_shpsts, lv_shpsts) > 0 THEN
IF lv_cstnum IS NULL THEN
In the after update trigger, I check certain criteria and if the criteria are met, I call the package/procedure mentioned above.
Code snippet of trigger:
IF lv_found = 'Y' THEN
IF custom_shipping_label.is_shipment_ltl_or_parcel(:old.ship_id) = 'S' THEN
IF :new.shpsts = lv_shpsts_staged AND INSTR (:old.shpsts, lv_shpsts_prev) = 0 THEN
In a SQL window in SQL developer I make sure the status of a particular shipment is R then I run the following statements
SET shpsts = 'S'
WHERE ship_id = '900001930';
Now when I go to my error table it has a new record with ship_id 900001930 (for which I ran the update statement), 'KKC Test' and the status displayed is still 'R'. So I know the package/procedure was called but because the status was not S it did not execute.
Is there any other way I can prove that I am updating or is there something I am doing wrong in what I have shown above?
Random thought, could this be a timing issue? The trigger fires and the procedure runs before the commit is issued. So, the question I can't answer is, does the procedure see the old row values or the new, uncomitted row values.
Could you just pass the value of :new.shpsts to the procedure and remove the need for the select statement inside the procedure?
Hi, I was thinking the same thing, that it's a timing issue and I could pass in the :new.shpsts. The only reason I was hoping to avoid passing in another parameter is that we are calling this procedure from another location and I was trying to avoid making too many changes to the exiting package. But I think there's no way around it.