CODEWITHSUNDEEP
oracle-databasetriggers
newbie
newbie

Reputation: 41

How to update after insert in the same table in an Oracle trigger?

I have a table with columns X, Y and Z. I want to set Z when I insert or update X and Y, so Z=X*Y, using a trigger.

create or replace trigger tr_tab
after insert
on tab
for each row
begin
update tab
set z=x*y;
end;
/

But after insert I get an error.

Upvotes: 1

Views: 35688

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You don't (and can't, generally) update the row you're inserting; you need to change what is being inserted by setting the new Z value for the current row:

create or replace trigger tr_tab
before insert or update
on tab
for each row
begin
  :new.z := :new.x * :new.y;
end;
/

This refers to the inserted/updated row using the :new pseudorecord syntax.

This needs to be a before-insert trigger to manipulate the :new value for a column; an after-insert for-each-row trigger fires to late to make that change.

You could have an after-insert statement level trigger - i.e. what you showed in the question but with the for each row line removed - but that would update Z for every row in the table even if nothing had changed, doing much more work and generating unnecessary redo. (Read more about the trigger types in the documentation).

You also have the option of making Z a virtual column that is generated automatically from the values of other columns, without needing a trigger.

Upvotes: 7

hmartos
hmartos

Reputation: 919

You have to reference the new inserted values to do that

update tab
set :new.z=:new.x*:new.y;

And if you want to difference between insertions and updates you can do it like this:

if (inserting or updating) then
        //Do things
end if;

All the information you need to create your trigger is in this page: http://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_tr.htm

Upvotes: 0

Related Questions