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.

Tony Andrews · Accepted Answer · 2011-07-18 16:28:23Z

13

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.)

edited Jul 18, 2011 at 16:28

answered Jul 18, 2011 at 12:53

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

5 Comments

Kevin Over a year ago

Sorry. Forgot to mention productId is a varchar2 and oid is int. Do I need to do some type conversion?

Rajesh Chamarthi Over a year ago

@Kevin - There will be implicit conversion (which will give errors if the product ID is not a perfect number), But is is always recommended you convert it explicitly whenever required. Also, WHY is product ID varchar2 if you will always store a number there?

Kevin Over a year ago

Hi. I got the error: ORA-04098: trigger 'MYPRODUCT_ID_TRG' is invalid and failed re-validation

Tony Andrews Over a year ago

In SQL Plus you can type "show errors trigger myproduct_id_trg" to see what the error is.

Kevin Over a year ago

Thanks a lot. This is solved. I just need to use := rather than = for Oracle.

Collectives™ on Stack Overflow

Oracle SQL trigger for automatically set a column value

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related