After Update Trigger Not Triggering
AbkNov 3 2010 — edited Nov 4 2010Dear all,
Pl help me to fix the error this trriger.
CREATE TRIGGER AUDIT_DEV.trg1
AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS
declare
AUDIT_PERSONS.EMP_USER_MODIFIED%TYPE;
AUDIT_PERSONS.EMP_DATE_MODIFIED%TYPE;
AUDIT_PERSONS.EMP_SK%TYPE;
AUDIT_PERSONS.EMP_ID%TYPE;
AUDIT_PERSONS.EMP_NAME%TYPE;
EMP_STATUS_OLD VARCHAR2(10);
EMP_STATUS_NEW VARCHAR2(10);
EMP_PERSONS.EMP_RESIGNATION_DATE%TYPE;
V_$SESSION.OSUSER%TYPE;
V_$SESSION.MACHINE%TYE;
V_$SESSION.USERNAME%TYPE;
BEGIN
select
sid,serial#,username,osuser,machine,logon_time into sid,serial#,username,osuser,machine,logon_time
from v$session where sid=(select sid from v$mystat where rownum=1);
INSERT INTO AUDIT_PERSONS_LOG
(EMP_USER_MODIFIED,EMP_DATE_MODIFIED,EMP_SK,EMP_ID,EMP_NAME,EMP_RESIGNATION_DATE,EMP_STATUS_OLD,EMP_STATUS_NEW,osuser,db_user,machine) VALUES
(
EMP_USER_MODIFIED,EMP_DATE_MODIFIED,EMP_SK,EMP_ID,EMP_NAME,EMP_RESIGNATION_DATE,:old.EMP_status,:new.EMP_status,osuser,db_user,machine
)
END;
/
Error report:
ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 - "NEW or OLD references not allowed in table level triggers"
*Cause: The trigger is accessing "new" or "old" values in a table trigger.
*Action: Remove any new or old references.
Edited by: Abk on Nov 4, 2010 11:09 AM