How to fix error after using trigger in Oracle

Question

I created trigger which should insert record into T_TABLE_B after T_TABLE_A changed(update) one column. But after updating T_TABLE_A, got error

ORA-04091: table T_TABLE_A is mutating, trigger/function may not see it

My code looks like this:

create or replace trigger TR_TABLE_B
after update on T_TABLE_A
for each row
begin
  insert into T_TABLE_B
  select * from T_TABLE_A 
  where EMAIL_DT is not null 
  and EMAIL_SENT='Y';
  commit;
end;

I expect to have insert to table B after table A got updated 2 columns(both tables has same structure and columns)

Thank you

See the docs: docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/… — Paul W
– Paul W, Commented Feb 25 at 0:17
So the code, as it is written now, will insert all rows from t_table_a in to t_table_b whenever a single row in t_table_a is updated. So if 5 rows are updated in t_table_a (or a single row is updated 5 times), then all rows from t_table_a will be inserted 5 times into t_table_b. I doubt that is the functionality you want. Pls update your question with the expected behaviour. — Koen Lostrie
– Koen Lostrie, Commented Feb 25 at 7:57

Scott · Accepted Answer · 2025-02-26 00:57:34Z

1

You are unable to query the table a row-level trigger is based on.

Use the :NEW.email_dt syntax to refer to column values for the newly inserted row. Depending on your exact needs, you can also use a WHEN clause to add a condition to your trigger.

create or replace trigger TR_TABLE_B
after update on T_TABLE_A
for each row
when (new.email_sent = 'Y')
begin
  insert into T_TABLE_B(id, email_dt, email_sent)
  values (:NEW.id, :NEW.email_dt, :NEW.email_sent);
end;

Commits are also not allowed/relevant within the trigger, as it's part of the grander transaction.

edited Feb 26 at 0:57

answered Feb 25 at 0:01

Scott

5,1303 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ina27 Feb 25 at 8:27

Thank you. Can you please show that in my example. I am new to triggers.

Scott Feb 26 at 0:58

Added example, including notion of WHEN clause - but that is just an alternative to an IF statement within the block, arguably slightly quicker/more readable.

Collectives™ on Stack Overflow

How to fix error after using trigger in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related