SQL trigger compiles correctly but throws error

0
votes

I am trying to create a trigger that copies the value from one column ("notubes" into another one ("notubesleft") of the same table when a new row is added. The trigger is compiled correctly but when I insert a new row, it throws an error.

My trigger is:

create or replace TRIGGER NOTUBESLEFT_INSERT_TRIGGER    
AFTER INSERT ON SAMPLES FOR EACH ROW BEGIN 
update samples
 set notubesleft = (select notubes from samples where sampleid = :new.sampleid);
 END;

When I try to commit a new row I get the error:

One error saving changes to table "APEX_WS_PROMETHEUS"."SAMPLES":
Row 10: ORA-04091: table APEX_WS_PROMETHEUS.SAMPLES is mutating, trigger/function may not see it
ORA-06512: at "APEX_WS_PROMETHEUS.NOTUBESLEFT_INSERT_TRIGGER", line 2 ORA-04088: error during execution of trigger 'APEX_WS_PROMETHEUS.NOTUBESLEFT_INSERT_TRIGGER'
ORA-06512: at line 1

I am asking a new question because I could find solutions on how to structure the PL/SQL code of the trigger, but I can't figure out why it does not work.

Thanks.

2
oracleoracle-sqldeveloper
Why are you trying to do this with an after-insert trigger? - Alex Poole
Because I thought I first needed to insert the values in the table, before being able to use them to populate another column. It is my first trigger. - Matte

2 Answers

1
votes

You can't (easily) query the table you're inserting into with a row-level trigger. But you shouldn't be querying the table at all, and shouldn't be using an after-insert trigger to do this. Use a before-insert trigger instead; you can get and set the values from the :NEW pseudorecord:

CREATE OR REPLACE TRIGGER NOTUBESLEFT_INSERT_TRIGGER    
BEFORE INSERT ON SAMPLES FOR EACH ROW
BEGIN 
  :new.notubesleft := :new.notubes;
END;
0
votes

You encountered mutating table problem. There is a restriction that you cannot query a table which is mutating in a FOR EACH ROW trigger

Trigger Restrictions on Mutating Tables

If you must update a mutating table, you can bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.