Re: trigger using a clause where with :NEW

From: ddf <oratune_at_msn.com>
Date: Tue, 8 Nov 2011 09:01:14 -0800 (PST)
Message-ID: <151001b1-d1fe-450c-ac81-b9c958116037_at_c18g2000yqj.googlegroups.com>

On Nov 8, 4:05 am, "grille12" <grill..._at_gmail.com> wrote:
> 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

I've guessed at the table structure and what I think you're trying to do; your example will eventually compile but will update the columns to NULL (I set the updated columns to NOT NULL to illustrate the point):

SQL> create table tablname(

  2          id      number,
  3          columnname1 varchar2(20) not null,
  4          columnname2 varchar2(20) not null,
  5          updatedt        date

  6 );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER NAMEOFTRIGGER   2 BEFORE INSERT OR UPDATE OF columnname1, columnname2 ON TABLNAME   3 FOR EACH ROW
  4
  5 BEGIN
  6 UPDATE TABLNAME SET COLUMNNAME1 = to_char(id) , COLUMNNAME2 =
  7 to_char(id) WHERE TABLNAME.id = :NEW.ID ;   8 END;
  9 /

Trigger created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> begin

  2          for i in 1..10 loop
  3                  insert into tablname
  4                  (id, updatedt)
  5                  values
  6                  (i, sysdate+mod(i,3));
  7          end loop;
  8
  9          commit;

 10
 11 end;
 12 /
begin
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BING"."TABLNAME"."COLUMNNAME1") ORA-06512: at line 3

SQL> Rewriting the trigger to process the row you have:

SQL> CREATE OR REPLACE TRIGGER NAMEOFTRIGGER   2 BEFORE INSERT OR UPDATE OF columnname1, columnname2 ON TABLNAME   3 FOR EACH ROW
  4
  5 BEGIN

  6          :new.columnname1 := to_char(:new.id);
  7          :new.columnname2 := to_char(:new.id);

  8 END;
  9 /

Trigger created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> begin

  2          for i in 1..10 loop
  3                  insert into tablname
  4                  (id, updatedt)
  5                  values
  6                  (i, sysdate+mod(i,3));
  7          end loop;
  8
  9          commit;

 10
 11 end;
 12 /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from tablname;

        ID COLUMNNAME1 COLUMNNAME2 UPDATEDT

---------- -------------------- -------------------- ---------
         1 1                    1                    09-NOV-11
         2 2                    2                    10-NOV-11
         3 3                    3                    08-NOV-11
         4 4                    4                    09-NOV-11
         5 5                    5                    10-NOV-11
         6 6                    6                    08-NOV-11
         7 7                    7                    09-NOV-11
         8 8                    8                    10-NOV-11
         9 9                    9                    08-NOV-11
        10 10                   10                   09-NOV-11

10 rows selected.

SQL> You will now get data where you would have had NULLs from your trigger.

David Fitzjarrell Received on Tue Nov 08 2011 - 11:01:14 CST