Last post May 13, 2013 01:53 AM by Ajay2707
Apr 24, 2013 10:10 AM|dpcodi|LINK
I am creating an audit log on several tables and I was wondering if it is possible to get the primary key/sequence.nextval of an newly inserted row from inside a trigger.
CREATE or REPLACE TRIGGER PAT_ORDERS$TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON ACCOUNT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
SELECT NVL(MAX(TRANSACTION_ID), 0) + 1
/* -- IF For Inserting -- */
IF INSERTING THEN
INSERT INTO ORDERS_AUDIT_LOG
(log_id, transaction_id, transaction_date, table_name,
table_pk_value, column_name, previous_value, changed_value,
((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM ORDERS_AUDIT_LOG),
v_transaction_id, SYSDATE, 'ACCOUNT',
:NEW.ID, -- Will this work?????
'ACCT_NAME', :OLD.ACCT_NAME, :NEW.ACCT_NAME,
May 12, 2013 01:48 AM|Prashant Kumar|LINK
Since your trigger fires on INSERT, UPDATE or DELETE, you should be using :OLD.ID
Other than this, it should work.
May 13, 2013 01:53 AM|Ajay2707|LINK
Oracle also give in-built factility of two table new and old (the same thing in sqlserver and known as magic table in sqlserver ).
please check how to use that in oracle.