I tried to perform dml in a table for auditing who perform dml where a after dml trigger also exists in the same table as per rule i tried to handel with autonomous_transaction but still cant perform.
Oracle plsql (trigger error)
please help me.
1 CREATE OR REPLACE TRIGGER autoinsert
2 AFTER INSERT OR UPDATE
3 ON triggertest
4 FOR EACH ROW
5 declare
6 pragma autonomous_transaction;
7 BEGIN
8 IF INSERTING THEN
9 insert into triggertest (ib,id) values(user,sysdate);
10 elsif UPDATING then
11 insert into triggertest(ub,ud) values(user,sysdate);
12 end if;
13 commit;
14* END;
SQL> /
Trigger created.
SQL> insert into triggertest(empno,ename,sa
insert into triggertest(empno,ename,sa
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT'
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT'
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT'
ORA-06512: at "SCOTT.AUTOINSERT", line 5
please help me.
1 CREATE OR REPLACE TRIGGER autoinsert
2 AFTER INSERT OR UPDATE
3 ON triggertest
4 FOR EACH ROW
5 declare
6 pragma autonomous_transaction;
7 BEGIN
8 IF INSERTING THEN
9 insert into triggertest (ib,id) values(user,sysdate);
10 elsif UPDATING then
11 insert into triggertest(ub,ud) values(user,sysdate);
12 end if;
13 commit;
14* END;
SQL> /
Trigger created.
SQL> insert into triggertest(empno,ename,sa
insert into triggertest(empno,ename,sa
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT'
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT'
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.AUTOINSERT'
ORA-06512: at "SCOTT.AUTOINSERT", line 5
ASKER
Thnks sir but the requrement is based on i have to save the audit information on same "triggertest" table column which contain (insert_user,insert_date,u
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks sir its useful , One of my another querie how can i create a standalone unit module for application using autonomous_transaction.
You're welcome ;-)
One of my another querie how can i create a standalone unit module for application using autonomous_transaction.Is that another question? Please clarify.
ASKER
Yeah , It's an another question related to standalon program unit for user software requrement.
Thnks
Thnks
Then you should open a new question here on EE ;-)
You're trying to insert into table "triggertest", but there is a trigger on that very table that tries to insert in itself???!!!!! Are you serious about that? I don't think so ;-) If you want to perfrom custom auditing a table, try to do that with the help of another table, e.g. triggertest_au. This table could have virtually all the columns from triggertest, plus fields for auditing information like audit_action (U,I,D), audit_user, audit_timestamp... whatever your needs are...