CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Severiano
Severiano

Reputation: 1093

trigger after insert on table

create or replace
trigger addpagamento
after insert on marcacoes_refeicoes
for each row
declare 
  nmarcacaoa number;
  ncartaoa number;
begin

  select nmarcacao into nmarcacaoa from marcacoes_refeicoes where rownum < (select count(*) from marcacoes_refeicoes);
  select ncartao into ncartaoa from marcacoes_refeicoes where rownum < (select count(*) from marcacoes_refeicoes);
  insert_pagamentos(nmarcacaoa, ncartaoa); --this is a procedure

  exception when others then
  raise_application_error(-20001, 'Error in Trigger!!!');

end addpagamento;

when i try to run the insert statement to the table "marcacoes_refeicoes" this procedure gives error: like the table is mutating

create or replace
procedure insert_pagamentos 
(nmarcacaoa in number, ncartaoa in number)
AS 
BEGIN
  insert into pagamentos (nmarcacao, datapagamento, ncartao) values (nmarcacaoa, sysdate, ncartaoa);
  commit;
END INSERT_PAGAMENTOS;

Upvotes: 0

Views: 355

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

You're hitting the mutating-table problem because you're selecting from the same table the trigger is on, but what you seem to be trying to do doesn't make sense. Your queries to get a value for nmarcacaoa and ncartaoa will return a no_data_found or too_many_rows error unless the table had exactly 2 rows in it before your insert:

select nmarcacao from marcacoes_refeicoes
where rownum < (select count(*) from marcacoes_refeicoes);

will return all rows except one; and the one that's excluded will be kind of random as you have no ordering. Though the state is undetermined within the trigger, so you can't really how many rows there are, and it won't let you do this query anyway. You won't normally be able to get a single value, anyway, and it's not obvious which value you actually want.

I can only imagine that you're trying to use the values from the row you are currently inserting, and putting them in your separate payments (pagamentos) table. If so there is a built-in mechanism to do that using correlation names, which lets you refer to the newly inserted row as :new (by default):

create or replace
trigger addpagamento
after insert on marcacoes_refeicoes
for each row
begin
  insert_pagamentos(:new.nmarcacaoa, :new.ncartaoa);
end addpagamento;

The :new is referring to the current row, so :new.nmarcacaoa is the nmarcacaoa being inserted. You don't need to (and can't) get that value from the table itself. (Even with the suggested autonomous pragma, that would be a separate transaction and would not be able to see your newly inserted and uncommitted data).

Or you can just do the insert directly; not sure what the procedure is adding here really:

create or replace
trigger addpagamento
after insert on marcacoes_refeicoes
for each row
begin
  insert into pagamentos(nmarcacao, datapagamento, ncartao)
  values (:new.nmarcacaoa, sysdate, :new.ncartaoa);
end addpagamento;

I've removed the exception handler as all it was doing was masking the real error, which is rather unhelpful.

Upvotes: 1

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

Editing this answer in view of the comments below:

You can use PRAGMA AUTONOMOUS_TRANSACTION to get rid of the error, but DONOT USE it as it will NOT solve any purpose.

Use PRAGMA AUTONOMOUS_TRANSACTION:

create or replace
trigger addpagamento
after insert on marcacoes_refeicoes
for each row
declare 
  PRAGMA AUTONOMOUS_TRANSACTION;
  nmarcacaoa number;
  ncartaoa number;
begin

  select nmarcacao into nmarcacaoa from marcacoes_refeicoes where rownum < (select count(*) from marcacoes_refeicoes);
  select ncartao into ncartaoa from marcacoes_refeicoes where rownum < (select count(*) from marcacoes_refeicoes);
  insert_pagamentos(nmarcacaoa, ncartaoa); --this is a procedure

  exception when others then
  raise_application_error(-20001, 'Error in Trigger!!!');

end addpagamento;

However in this case , select count(*) from marcacoes_refeicoes will give you the new count after the current insertion into the table.

Upvotes: 0

Guntram Blohm
Guntram Blohm

Reputation: 9819

Short (oversimplified) answer:

You can't modify a table in a trigger that changes the table.

Long answer:

http://www.oracle-base.com/articles/9i/mutating-table-exceptions.php has a more in-depth explanation, including suggestions how to work around the problem.

Upvotes: 1

Related Questions