pl sql error when working with trigger

Question

I get an error when working with the following trigger:

create or replace trigger t1
  after insert or update  
     on student_tbl 
  declare
   pragma autonomous_transaction;
    begin
   if inserting then

 insert into stud_fees_details(stud_id,fees_balance,total_fees) 
       select stud_id,course_fees,course_fees from student_tbl s,courses_tbl c where s.stud_standard_id=c.course_id;

 elsif updating('stud_standard_id') then

insert into stud_fees_details(stud_id,fees_balance,total_fees) 
select stud_id,course_fees,course_fees from student_tbl s,courses_tbl c where s.stud_standard_id=c.course_id;

 end if;
end;

error is

ORA-06519: active autonomous transaction detected and rolled back ORA-06512: at "SYSTEM.T1", line 15 ORA-04088: error during execution of trigger 'SYSTEM.T1'

Removed Sql server and Mysql tag's since the error message clearly states it is ORACLE — Pரதீப்
– Pரதீப், Commented Jan 17, 2016 at 12:59
Did you try searching for the error message "active autonomous transaction detected and rolled back" on a search engine? My first hit seems to explain it in a very detailed way. — DIDoS
– DIDoS, Commented Jan 17, 2016 at 13:01
Ther is the oracle Database Error Messages manual that explains why this error occurs and how to avoid it. — miracle173
– miracle173, Commented Jan 17, 2016 at 13:59
Using an autonomous transaction in a trigger sounds like a bad idea in the first place. What is the real problem you are trying to solve with that? — user330315
– user330315, Commented Jan 17, 2016 at 15:15
This seems to be a so called XY-problem: you introduced autonomous transactions because you had another problem. Now you ask about the autonomous transaction error instead of your original problem. Besides that, is is a bad idea to create objects in the SYSTEM schema. You should create a user/schema for this purpose. — miracle173
– miracle173, Commented Jan 18, 2016 at 8:40

Community · Accepted Answer · 2017-05-23 12:23:28Z

Database Error Messages

ORA-06519: active autonomous transaction detected and rolled back
Cause: Before returning from an autonomous PL/SQL block, all autonomous transactions started within the block must be completed (either committed or rolled back). If not, the active autonomous transaction is implicitly rolled back and this error is raised.
Action: Ensure that before returning from an autonomous PL/SQL block, any active autonomous transactions are explicitly committed or rolled back.

Example from Database PL/SQL Language Reference

-- Autonomous trigger on emp table:

CREATE OR REPLACE TRIGGER log_sal
  BEFORE UPDATE OF salary ON emp FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log (
    log_id,
    up_date,
    new_sal,
    old_sal
  )
  VALUES (
    :old.employee_id,
    SYSDATE,
    :new.salary,
    :old.salary
  );
  COMMIT;
END;
/

But @a_horse_with_no_name already stated that an autonomous transaction maybe is is not appropriate here.

After removing the autonomous transaction pragma you maybe will run into the problem that @GordonLinoff adresses with his post.

Gordon Linoff · Accepted Answer · 2016-01-17 15:26:30Z

1

If a trigger doesn't use :new or :old, then it is suspicious. Your trigger is using the same table being modified in queries.

You probably intend:

create or replace trigger t1 after insert or update on student_tbl 
declare
   pragma autonomous_transaction;
begin
    if inserting then
        insert into stud_fees_details(stud_id, fees_balance, total_fees) 
            select stud_id, course_fees, course_fees
            from courses_tbl c
            where c.course_id = :new.stud_standard_id;

    elsif updating('stud_standard_id') then
        insert into stud_fees_details(stud_id, fees_balance, total_fees) 
            select stud_id, course_fees, course_fees
            from courses_tbl c
            where c.course_id = :new.stud_standard_id;
    end if;
    commit;
end;

Notes:

The select statements should have table aliases, to distinguish the columns that come from :new and from courses_tbl.
The two clauses to the if look the same to me, so I don't understand the logic.
The join conditions between something called course_id and somethign called stud_standard_id looks suspicious. I would advise you to name foreign keys after the primary key they refer to.

edited Jan 17, 2016 at 15:26

answered Jan 17, 2016 at 13:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

6 Comments

miracle173 Over a year ago

nothing in this answer will solve the problem. You trigger will raise the same error, too.

Gordon Linoff Over a year ago

@miracle173 . . . This code should prevent the mutating table error, which is the likely cause of the failure. This code no longer references the table being modified. Why do you think this doesn't solve the problem?

miracle173 Over a year ago

because the problem is the autonomous transaction that is neither commited nor rolled back

Alex Poole Over a year ago

@miracle173 - I'd wager that the pragma was only put there in the first place to try to avoid a mutating table error, which was only happening because of the unnecessary query against the table. I don't believe this version needs that pragma any more, or therefore the commit; but it does need to be a for each row trigger to reference :new. (It also doesn't really need the branch as both do the same thing, but that's another issue entirely).

miracle173 Over a year ago

@AlexPoole In my answer I conjectured that the autonomous transaction is unnecessary , too. Nevertheless the reason for this error message is the autonomous transaction that is not finished correctly..

|

Collectives™ on Stack Overflow

pl sql error when working with trigger

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related