Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Unanswered: table trigger question

    Hi,

    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?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    COMMIT after setting the status to "S".

  3. #3
    Join Date
    Nov 2007
    Posts
    14
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I can tell, committed data is visible to everyone. So, if you really have committed that update and later selected data from the same table, it is expected that you'll see that change.

    Please, provide copy-paste of your SQL*Plus session which proves that
    a) you have updated a column
    b) committed
    c) executed a procedure which does not see that change.

  5. #5
    Join Date
    Nov 2007
    Posts
    14
    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:
    Code:
    BEGIN
       
          gv_ship_id := av_shipment_id;
          
          SELECT rtstr1
            INTO lv_valid_shpsts
            FROM poldat
           WHERE polcod = 'UC_INSTANCE_CONSTANTS'
             AND polvar = 'AIAG_LABELS'
             AND polval = 'GENERATE_LABEL_STATUS';
             
          SELECT shpsts
            INTO lv_shpsts
            FROM shipment
           WHERE ship_id = av_shipment_id;
           
           insert_lodser_err_data
                (
                av_shipment_id,
                'KKC Test',
                lv_shpsts
                );
           
          IF INSTR (lv_valid_shpsts, lv_shpsts) > 0 THEN
             
             SELECT DECODE(get_cstnum(av_shipment_id),NULL,get_soms_cstnum(av_shipment_id),
                                                         get_cstnum(av_shipment_id))
               INTO lv_cstnum
               FROM dual;
          
             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:
    Code:
    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
                custom_shipping_label.select_lblgrp_info(:old.ship_id);
             END IF;
             
          END IF;
          
       ELSE
    In a SQL window in SQL developer I make sure the status of a particular shipment is R then I run the following statements

    Code:
    
    UPDATE shipment
         SET shpsts = 'S'
      WHERE ship_id = '900001930';
    
    COMMIT;
    
    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?

  6. #6
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    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?

  7. #7
    Join Date
    Nov 2007
    Posts
    14
    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.

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •