1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Compound Triggers

Discussion in 'SQL PL/SQL' started by Bharat, May 21, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    I have just created one compound trigger and tried to use :new field at one certain place. But it is showing error. Please help me how to use that to get the value. Sample trigger is shown below:


    Code (Text):
    TRIGGER TRG_GET_SAL
       FOR INSERT ON CSI_ITEM_INSTANCES
       
    COMPOUND TRIGGER
         ID OE_ORDER_LINES_ALL.LINE_ID%TYPE;
         ATTR16 OE_ORDER_LINES_ALL.ATTRIBUTE16%TYPE;
     BEFORE STATEMENT IS
       BEGIN
         SELECT LINE_ID,ATTRIBUTE16
         INTO ID,ATTR16
         FROM OE_ORDER_LINES_ALL
         WHERE LINE_ID=:NEW.LAST_OE_ORDER_LINE_ID;  
     END BEFORE STATEMENT;
     AFTER STATEMENT IS
     BEGIN
    UPDATE CSI_ITEM_INSTANCES SET ATTRIBUTE3 = attr16 WHERE LAST_OE_ORDER_LINE_ID = ID;
     END AFTER STATEMENT;
      END TRG_GET_SAL;
     
    While compiling the following error is displaying.

    So is there any solution for this to retrieve that value.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    what is :NEW in
    Is it the data block name?
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    That was the value taken from trigger table (CSI_ITEM_INSTANCES) which is ready to insert into the trigger table. Iam not much familiar with that trigger so I dont think that was an exact meaning for :new.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Where are you trying to use this code, in Forms Or PL/SQL ???
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Actually the trigger is raised when table row is inserted. But that insertion is happening when we ship the order or close the order. The code is written in PL/SQL itself.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, a BEFORE EACH ROW section needs to be declared in the trigger. the :NEW and :OLD blocks are not available if the trigger is not declared as an EACH ROW trigger.

    Code (SQL):
    BEFORE EACH ROW IS
       BEGIN
         SELECT LINE_ID,ATTRIBUTE16
         INTO ID,ATTR16
         FROM OE_ORDER_LINES_ALL
         WHERE LINE_ID=:NEW.LAST_OE_ORDER_LINE_ID;  
     END BEFORE STATEMENT;
     
     
    Bharat and kiran.marla like this.
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Thanks David That works fine. But here in my case we are trying to do for shippable item and for this type of items the trigger is not working. When we are trying with non-shippable item thats working fine. Can any one suggest me on this. I have provided my trigger below:


    Code (Text):
    TRIGGER TRG_GET_SAL
       FOR INSERT ON CSI_ITEM_INSTANCES
       
    COMPOUND TRIGGER                                       -- This is compound trigger
         ID OE_ORDER_LINES_ALL.LINE_ID%TYPE;
         ATTR16 OE_ORDER_LINES_ALL.ATTRIBUTE16%TYPE;
     BEFORE EACH ROW IS
       BEGIN
         SELECT LINE_ID,ATTRIBUTE16
         INTO ID,ATTR16
         FROM OE_ORDER_LINES_ALL
         WHERE LINE_ID=:NEW.LAST_OE_ORDER_LINE_ID;   -- To get the attribute3 value from oe_order_lines_all table
         
         :new.attribute3:=attr16;         -- To update the attribute3
     END BEFORE EACH ROW;
     

    END TRG_GET_SAL;
    here attribute3 is added in csi_item_instances table and attribute16 is added in oe_orde_lines_all table. Now we want to update attribute3 in csi_item_instances table with the value which is provided in attribute16 field in oe_order_lines_all table. But the problem here is this is working fine with non-shippable items and not working with shippable items.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We don't know what your non-shippable and shippable items are, nor do we know how those records are flagged in this table. You will need to provide much more information than this if you want useful assistance.
     
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi david,

    I can't make it long to express that setups, but to say shortly shippable items are just created by using purchased items template and track installed base is checked and non-shippable items too created by using purchased items template but removed shippable flag in order management tab and track installed base is checked in this item too. There is no much changes exists for those two items.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That doesn't help, really; can you show an examples of a shippable record and non-shippabler record?