• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

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;
/
0
diannagibbs
Asked:
diannagibbs
  • 2
1 Solution
 
Swadhin RaySenior Technical Engineer Commented:
Check in combination part from this link:

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

0
 
JacobfwCommented:
You need to specify the "old" or "new" value of the column that composes the PK from the record being inserted and not the variable v_col1

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
     ( new.col1,
       sysdate,
       'I');
END IF;
IF UPDATING THEN
    INSERT INTO NEW_TABLE
     ( col1,
       triggerdate,
       type)
    VALUES
     ( new.col1,
       sysdate,
       'U');
END IF;
IF DELETING THEN
    INSERT INTO NEW_TABLE
     ( old.col1,
       triggerdate,
       type)
    VALUES
     ( v_col1,
       sysdate,
       'D');
END IF;
END;
/
0
 
diannagibbsAuthor Commented:
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?  
0
 
diannagibbsAuthor Commented:
Thanks to all who replied.  I was able to modify and make it work.  Appreciate it!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now