Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

After Update Trigger Not Triggering

AbkNov 3 2010 — edited Nov 4 2010
Dear 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
This post has been answered by Saubhik on Nov 4 2010
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 2 2010
Added on Nov 3 2010
8 comments
783 views