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!

Change column data is inserted into when running After Insert row level trigger...

Discussion in 'SQL PL/SQL' started by MFriend, Jun 19, 2014.

  1. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi Folks:

    I am using an AFTER INSERT row trigger in Oracle 11g to copy specific columns from one table to another on insert. I have the trigger and insert working ok. The problem I have is that I need to insert the new data from one column to a different column when copying it.

    The trigger info reads:

    BEGIN insert into BALES_STORAGE (CROP, CUTTING, DESTINATION, BALES_MOVED, DATE_MOVED, PASTURE, TARGET_LB_PER_DAY) values :)new.CROP, :new.CUTTING, :new.MOVING_LOCATION, :new.BALES_MOVED, :new.DATE_MOVED, :new.PASTURE, :new.TARGET_LB_PER_DAY); END;

    The first table is called "BALES_HARVESTED" and the 2nd table the trigger inserts the selected columns into is called "BALES_STORAGE". I need to insert the :new.MOVING_LOCATION data into the column called DESTINATION on the second table.

    So my question is: when using an after insert row trigger, how to I change the column that the data is inserted into?

    Thanks for any help.

    Matthew
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Change column data is inserted into when running After Insert row level trigger..

    I don't know why you're asking the question -- your insert statement does just what you expect it to do. Looking at an example:


    Code (SQL):

    SQL> CREATE TABLE bales_harvested(
      2          crop            varchar2(40),
      3          cutting         NUMBER,
      4          moving_location varchar2(45),
      5          bales_moved     NUMBER,
      6          date_moved      DATE,
      7          pasture         varchar2(45),
      8          target_lb_per_day       NUMBER);


    TABLE created.


    SQL>
    SQL> CREATE TABLE bales_storage(
      2          crop            varchar2(40),
      3          cutting         NUMBER,
      4          destination     varchar2(45),
      5          bales_moved     NUMBER,
      6          date_moved      DATE,
      7          pasture         varchar2(45),
      8          target_lb_per_day       NUMBER);


    TABLE created.


    SQL>
    SQL> CREATE TRIGGER pop_bales_storage
      2  after INSERT ON bales_harvested
      3  FOR each ROW
      4  BEGIN
      5   INSERT INTO BALES_STORAGE (CROP, CUTTING, DESTINATION, BALES_MOVED, DATE_MOVED, PASTURE, TARGET_LB_PER_DAY)
      6   VALUES (:NEW.CROP, :NEW.CUTTING, :NEW.MOVING_LOCATION, :NEW.BALES_MOVED, :NEW.DATE_MOVED, :NEW.PASTURE, :NEW.TARGET_LB_PER_DAY);
      7  END;
      8  /


    TRIGGER created.


    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> SELECT *
      2  FROM bales_harvested;


    no ROWS selected


    SQL>
    SQL> SELECT *
      2  FROM bales_storage;


    no ROWS selected


    SQL>
    SQL> INSERT ALL
      2  INTO bales_harvested
      3  VALUES('HAY', 1, 'BARN #4', 77, sysdate, 'FIELD #2', 3500)
      4  INTO bales_harvested
      5  VALUES('HAY', 2, 'BARN #4', 93, sysdate, 'FIELD #7', 4500)
      6  SELECT * FROM dual;


    2 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT *
      2  FROM bales_harvested;


    CROP                    CUTTING MOVING_LOCATION      BALES_MOVED DATE_MOVE PASTURE                   TARGET_LB_PER_DAY
    -------------------- ---------- -------------------- ----------- --------- ------------------------- -----------------
    HAY                           1 BARN #4                       77 19-JUN-14 FIELD #2                       3500
    HAY                           2 BARN #4                       93 19-JUN-14 FIELD #7                       4500


    SQL>
    SQL> SELECT *
      2  FROM bales_storage;


    CROP                    CUTTING DESTINATION          BALES_MOVED DATE_MOVE PASTURE                   TARGET_LB_PER_DAY
    -------------------- ---------- -------------------- ----------- --------- ------------------------- -----------------
    HAY                           1 BARN #4                       77 19-JUN-14 FIELD #2                       3500
    HAY                           2 BARN #4                       93 19-JUN-14 FIELD #7                       4500


    SQL>
     

    Your insert statement lists the columns to populate; if they're named differently from the source columns that isn't a problem as long as the columns are declared as the same type and the destination column isn't any smaller than the source. What you have written does just what you want it to do.