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

On Nov 8, 4:05*am, "grille12" <grill... (AT) gmail (DOT) com> wrote:
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

ddf wrote:

When I first learned about relational database theory, there was something about removing
redundancy. That was a long time ago. Times have changed.

On Nov 8, 10:30*am, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
I agree; I don't recommend duplicating data in a table (or across
tables if it can be avoided). I probably should have stated that but
I treated this as an example.


David Fitzjarrell

ddf wrote:
Which you did well. I didn't even notice he was trying to update the table being updated, until
I saw your solution.