diannagibbs
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;
/
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
Thanks to all who replied. I was able to modify and make it work. Appreciate it!
http://www.deltatelecom.ru/ora$doc/7/DOC/server/doc/SCN73/ch15.htm