After Update Trigger Not Triggering

Abk

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

Saubhik

What is the result of

SELECT * FROM user_triggers
WHERE trigger_name='your_trigger_name';

Specially note the status column.

Saubhik

CREATE TRIGGER AUDIT_DEV.trg1
AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS
FOR EACH ROW

789895

Hi,

Try this

CREATE TRIGGER AUDIT_DEV.trg1
AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS 

Include these statements

REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW or FOR EACH STATEMENT

cheers

VT

Abk

Thanks for your replies Saubhik/VT,

now my trigger is compiled. but is not triggering. pl help me to resolve .....

create or replace
TRIGGER AUDIT_DEV.trg2
AFTER DELETE OR UPDATE OF EMP_STATUS ON AUDIT_DEV.AUDIT_PERSONS
FOR EACH ROW
declare

OSUSER varchar2(30);
MACHINE varchar2(30);
logon_time date;
db_user varchar2(30);
USERNAME VARCHAR2(30);
EMP_USER_MODIFIED AUDIT_PERSONS.EMP_USER_MODIFIED%TYPE;
EMP_DATE_MODIFIED AUDIT_PERSONS.EMP_DATE_MODIFIED%TYPE;
EMP_SK AUDIT_PERSONS.EMP_SK%TYPE;
EMP_ID AUDIT_PERSONS.EMP_ID%TYPE;
EMP_NAME AUDIT_PERSONS.EMP_NAME%TYPE;
EMP_RESIGNATION_DATE AUDIT_PERSONS.EMP_RESIGNATION_DATE%TYPE;

BEGIN

select username,osuser,machine,logon_time into db_user,osuser,machine,logon_time from v$session where sid=(select sid from v$mystat where rownum=1);
INSERT INTO AUDIT_DEV.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 );
COMMIT;
END;
/


09:59:06 [email protected]>UPDATE AUDIT_DEV.AUDIT_PERSONS SET EMP_STATUS='TEST' WHERE EMP_ID='4234';

EMP_STATUS
---------------
TEST

1 row selected.

Elapsed: 00:00:00.01
10:00:03 [email protected]>commit;

Commit complete.

Elapsed: 00:00:00.01
10:00:17 [email protected]>select * from AUDIT_persons_log;

no rows selected

Elapsed: 00:00:00.00

10:00:17 [email protected]>

Edited by: Abk on Nov 4, 2010 10:42 AM

Saubhik

Remove COMMIT from the trigger body.

Abk

i had removed the commit; , still not triggering. Pl someone help me to solve...

Edited by: Abk on Nov 4, 2010 2:06 PM

Edited by: Abk on Nov 4, 2010 2:06 PM

Saubhik

What is the result of

SELECT * FROM user_triggers
WHERE trigger_name='your_trigger_name';

Specially note the status column.

Abk

Thanks for your reply, Saubhik

here i found in the query output column_name is blank.




OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNER BASE_OBJECT_TYPE TABLE_NAME
------------ ------------------------------ ---------------- -------------------- ------------------------------ ---------------- ---------------------------------
DEV TRG2 AFTER EACH ROW UPDATE OR DELETE DEV TABLE AUDIT_PERSONS




COLUMN_NAME REFERENCING_NAMES
-------- -------------------- --------------------------------------
REFERENCING NEW AS NEW OLD AS OLD

STATUS DESCRIPTION ACTION_TYPE TRIGGER_BODY
----------------- -------- -------------------------------------------------- ----------- ---------------------
ENABLED DEV.trg2 PL/SQL declare
AFTER DELETE OR UPDATE OF PRS_STATUS ON DEV.AUDIT
PERSONS -- OSUSER varchar2(30);
FOR EACH ROW -- MACHINE varchar2(30);
-- logon_time date;
-

635471

Instead of selecting values into variables and then inserting using a values clause, can you not just run an insert statement that selects the values you want?