Re: Please help with Sequences and Triggers

Joseph,

Try the following for your trigger.

 CREATE OR REPLACE TRIGGER AUTOINCREMENT  BEFORE INSERT ON JOURNALISTS
 FOR EACH ROW
 DECLARE
 stmt varchar2(100);
 BEGIN
   stmt := 'SELECT PARTID.NEXTVAL FROM DUAL';    EXECUTE IMMEDIATE stmt INTO :NEW.ID;
 END; Hth,

Cliff

"Joseph Mak" <jhbmak_at_yahoo.com> wrote in message news:fRUZ5.50084$Wq1.19727107_at_nnrp5-w.sbc.net...
> hello,
>
> i am migrating a portion of a access database into oracle and am having
> trouble resolving the autoid datatype found in access. i have read from
> several posts archieved in deja that have pointed me in the direction of
> sequences and triggers. i have created the following sequence, table and
> trigger.. but i get ORA-04098: trigger 'AUTOINCREMENT' is invalid and
 failed
> re-validation error.
>
> the following is the sql i used. can anybody please help?
>
> would appreciate any help. thank you.
>
> joe
>
> --
> CREATE SEQUENCE PARTID
> INCREMENT BY 1
> START WITH 1
> NOCACHE
>
> CREATE TABLE JOURNALISTS
> (ID NUMBER,
> PASSWORD VARCHAR(40),
> FIRSTNAME VARCHAR(40),
> LASTNAME VARCHAR(40),
> EMAIL VARCHAR(40),
> PUBLICATION VARCHAR(40),
> LASTLOGIN DATE )
>
> CREATE OR REPLACE TRIGGER AUTOINCREMENT
> BEFORE INSERT ON JOURNALISTS
> FOR EACH ROW
> BEGIN
> SELECT PARTID.NEXTVAL
> INTO :NEW.ID
> FROM DUAL
> END
>
>
> --
> open a window into an alternate paradigm _at_ http://window.com/~jmak
> jhbmak_at_yahoo.com
>
>
Received on Thu Dec 14 2000 - 06:38:32 CET