CODEWITHSUNDEEP
oracle-databaseplsqltriggers
MarcusWilliams
MarcusWilliams

Reputation: 1

Trigger not inserting records into table

I have a trigger but it's not inserting the records into target table.

create or replace trigger ins_det_trig1
after insert on Table_a
declare
pragma    autonomous_transaction;
---
begin
insert into inf_det
select 
a.loc_id,
a.genre_id,
to_char(a.emp_date,'yyyy-mm-dd'),
a.san_seq
from Table_b a ,
Table_a b
where 
b.emp_date=a.emp_date
and   b.genre_id=a.genre_id
and   b.san_seq=a.san_seq;

commit;
exception
when others
Rollback;
end;

pls help me on this

Upvotes: 0

Views: 470

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

First of all the trigger is invalid, so it won't do anything. You are using a variable named v_err that you don't declare.

Then, you are swallowing all exceptions. If something goes wrong, you fill that variable with the error code and then silently end the trigger. Thus you'll never get informed when the trigger fails.

But the main problem is that you are not using the trigger as you should use triggers in Oracle. The trigger is an after row trigger (AFTER INSERT ... FOR EACH ROW) and hence fires once per row on inserts of table_a rows. The values that got inserted in a new row can be accessed with :new, e.g. :new.business_date.

You, however, ignore these values and select from the table instead. But at the moment of your select the table is mutating. Let's say we write an insert statement that inserts two rows. The trigger fires two times. It is left to chance which row gets inserted first. Oracle sees this and when you are inside the trigger and try to select, it tells us that the content of the table is not deterministic, as the other row may already be in the table or not. We get a mutating table exception.

Having said this: It seems you want an after statement trigger. A trigger that fires after the insert of the rows is complete. For this to happen remove the FOR EACH ROW clause and the related REFERENCING clause, too.

create or replace trigger ins_det_trig1
after insert on Table_a
declare
  ...
begin
  ...
end;

Upvotes: 1

Related Questions