Oracle Trigger help - Very basic
Hello, this is what am trying to achieve
Capture OSUser name who is making changes to table data (from an application tied to LDAP) and move the whole record (when INSERT UPDATE DELETE) to an audit table
Parent table
CREATE TABLE MASTER_TAB
(
COL1 VARCHAR2(3 BYTE)
);
History table
CREATE TABLE MASTER_TAB_HST
(
COL1 VARCHAR2(3 BYTE),
OSUSER VARCHAR2(5 BYTE)
)
Trigger on parent table
CREATE OR REPLACE TRIGGER INVITRO.TRG_master_tab
BEFORE INSERT OR UPDATE
ON master_tab
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
INSERT INTO master_tab_hst VALUES (:NEW.col1,
SELECT SYS_CONTEXT('USERENV', 'OS_USER')
INTO :NEW.OSUSER
FROM dual;
ELSIF UPDATING THEN
INSERT INTO master_tab_hst VALUES (:NEW.col1,
SELECT SYS_CONTEXT('USERENV', 'OS_USER')
INTO :NEW.OSUSER
FROM dual;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Consider the error and then re-raise
RAISE;
END ;
Getting error when creating the trigger, I know am doing something wrong here.
Help much appreciated.
-Srini
