Re: Pro*C and trigger

lomba wrote:

> Hi !
>
> I've a big problem :(
> I have a table :
> table1(id INT, name CHAR(20));
> I made a view on this table (due to project constraints) :
> view1(name)
>
> I'd like, when users insert in view1 that the ID will be automaticaly
> incremented. So I use a trigger :
> CREATE OR REPLACE TRIGGER trig
> BEFORE INSERT ON table1
> FOR EACH ROW
> BEGIN
> SELECT sequence.nextval INTO :NEW.id FROM DUAL;
> END;
>
> No problem if I insert and create trigger manualy.
> But with Pro*C, it doesn't work !
> It seems to be impossible to use ":" before NEW because the precompiler
> believes it's an host variable.
> So I do :
> EXEC SQL CREATE ....
> BEGIN
> SELECT sequence.nexval INTO NEW.id FROM DUAL;
> END;
> END-EXEC;
>
> No problem during the creation (sqlca is ok).
> But when I try to insert in the view, i've the message:
> ORA-04098 : trigger 'TRIG' is invalid and failed revalidation
>
> I think it's due to "NEW" but how could I do ???
> Thanks for your help
> --
> lomba

I would strongly advise against creating triggers on-the-fly and using CHAR fields.

Have you carefully thought out what you are doing and the implications thereof?

Daniel Morgan Received on Mon Dec 09 2002 - 11:09:39 CST