Link to home
Start Free TrialLog in
Avatar of diannagibbs
diannagibbsFlag for United States of America

asked on

Oracle trigger inserting PK value, date and trigger value into second table when update/insert/delete

Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table.  I have the trigger and it works except it is not loading my col1/PK values.  I cannot figure out exactly what I need to do and why this is not working or maybe a better/easier way to do this?  Any direction appreciated.  Col1 is my PK on Table that I  want to load anytime there is an update/delete/insert on the table.
My code:
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
    ON TABLE_NAME
    FOR EACH ROW
DECLARE
  v_col1 TABLE_NAME.COLUMN%type;
BEGIN
IF INSERTING THEN
    INSERT INTO NEW_TABLE
     ( col1,
       triggerdate,
       type)
    VALUES
     ( v_col1,
       sysdate,
       'I');
END IF;
IF UPDATING THEN
    INSERT INTO NEW_TABLE
     ( col1,
       triggerdate,
       type)
    VALUES
     ( v_col1,
       sysdate,
       'U');
END IF;
IF DELETING THEN
    INSERT INTO NEW_TABLE
     ( col1,
       triggerdate,
       type)
    VALUES
     ( v_col1,
       sysdate,
       'D');
END IF;
END;
/
Avatar of Swadhin Ray
Swadhin Ray
Flag of United States of America image

Check in combination part from this link:

http://www.deltatelecom.ru/ora$doc/7/DOC/server/doc/SCN73/ch15.htm

ASKER CERTIFIED SOLUTION
Avatar of Jacobfw
Jacobfw
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of diannagibbs

ASKER

I understand this is what I need to do but can you give me an example?  And which example from link above should I follow?  
Thanks to all who replied.  I was able to modify and make it work.  Appreciate it!