trigger using a clause where with :NEW

From: grille12 <grille12_at_gmail.com>
Date: Tue, 8 Nov 2011 13:05:30 +0100
Message-ID: <4eb91b0b$0$18804$426a74cc_at_news.free.fr>



Hello,

I am building a trigger such as below:

CREATE OR REPLACE TRIGGER "SCHEMA"."nameoftrigger" BEFORE INSERT OR UPDATE OF "column", "column" ON "TABLENAME" FOR EACH ROW

BEGIN
UPDATE TABLENAME SET COLUMNNAME1 = to_char(column) , COLUMNNAME2 = to_char(column) WHERE TABLENAME.column = :NEW.ID ; END; I am trying to do a simple thing (normally). I want to update 2 (nvarchar2) columns in my table with a (number) value that I retrieve using the new id updated.
Each time I want to apply this I have the (in)famous message "Missing IN or OUT parameter at index::1"

I stripped down my request to this:
BEGIN
UPDATE ATTR_PRODUCT SET PID = 4 , TID = 5 WHERE ATTR_PRODUCT.ID= 100; END; and it works.
When I modify the WHERE clause with WHERE ATTR_PRODUCT.ID= :NEW.ID; it fails. I believed the :NEW.id was allowed when using FOR EACH ROW

I am using Oracle 11G enterprise 11.1.0.7.0 64bits.

Thanks for your help with this

Wilfried Received on Tue Nov 08 2011 - 06:05:30 CST

Original text of this message