Oracle SQL trigger for automatically set a column value

Question

I am writing a Oracle trigger. This trigger should automatically set the value of the column "productId" to be the oid of the row just inserted.

The trigger I wrote is:

create or replace trigger MyProduct_id_trg 
after insert on MyProduct
begin 
   update MyProduct set productId = inserted.oid where oid = inserted.oid;
end; 

However, this does not work.

Can someone help me with this?

Regards.

Answer 1

Looks like you are trying to use SQL Server syntax on an Oracle database! Try this:

create or replace trigger MyProduct_id_trg 
before insert on MyProduct
for each row
begin 
   :new.productId := :new.oid;
end; 

(Note: before not after, and with for each row.)