Re: Pro*C and trigger

"lomba" <news_at_pressemicro.net> wrote in message news:1039283429.33820.0_at_dyke.uk.clara.net...
> 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
>
>

You'll need dynamic sql and/or execute immediate as this is DDL. In this case your create trigger command ends up in a string and you can use concatenation
'....'+chr(numeric equivalent for :) + '....'

I would however strongly advise against creating triggers on the fly.

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address