CODEWITHSUNDEEP
sqldatabaseoracle-databasetriggers
Sergio Izquierdo
Sergio Izquierdo

Reputation: 333

Values of the inserted row in a Trigger Oracle

I want a trigger that updates the value of a column, but I just want to update a small set of rows that depends of the values of the inserted row. My trigger is:

CREATE OR REPLACE TRIGGER example
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
UPDATE table1 t
SET column2 = 3
WHERE t.column1 = :new.column1;
END;
/

But as I using FOR EACH ROW I have a problem when I try it, I get the mutating table runtime error. Other option is not to set the FOR EACH ROW, but if I do this, I dont know the inserted "column1" for comparing (or I dont know how to known it).

What can I do for UPDATING a set of rows that depends of the last inserted row?

I am using Oracle 9.

Upvotes: 1

Views: 6582

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

If you need to update multiple rows in table1 when you are updating one row, then you would seem to have a problem with the data model.

This need suggests that you need a separate table with one row per column1. You can then fetch the value in that table using join. The trigger will then be updating another table, so there will be no mutation problem.

Upvotes: 2

Gananjay Thanekar
Gananjay Thanekar

Reputation: 11

`create table A 
 (
  a INTEGER,
  b CHAR(10)
 );
 create table B
 (
  b CHAR (10),
  d INTEGER 
 );
 create trigger trig1
 AFTER INSERT ON A
 REFERENCING NEW AS newROW
 FOR EACH ROW
 when(newROW.a<=10)
 BEGIN 
 INSERT into B values(:newROW.b,:newROW.a);
 END trig1;
 insert into A values(11,'Gananjay');
 insert into A values(5,'Hritik');

 select * from A;
 select * from B;`

Upvotes: 1

0xdb
0xdb

Reputation: 3697

You should avoid the DML statements on the same table as defined in a trigger. Use before DML to change values of the current table.

create or replace trigger example
before insert on table1
for each row
begin
    :new.column2 := 3;
end;
/

You can modify the same table with pragma autonomous_transaction:

create or replace trigger example
after insert on table1 for each row
declare
    procedure setValues(key number) is
        pragma autonomous_transaction;
    begin
        update table1 t
        set column2 = 3
        where t.column1 = key
        ;
    end setValues;    
begin
    setValues(:new.column1);
end;
/

But I suggest you follow @GordonLinoff answere to your question - it's a bad idea to modify the same table in the trigger body.

See also here

Upvotes: 4

Related Questions