Re: Please help with Sequences and Triggers

This defenitly works:

Create sequence seq_xxx increment by 1;

CREATE OR REPLACE TRIGGER [Trigger Name]  BEFORE
 INSERT
 ON [Table Name]
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
declare

    v_NewID number;

begin

     select seq_xxx.nextval into v_NewID from dual;

     :new.ID := v_NewID;

end;
/

Jochen

Cliff Dabrowski <cliffdw_at_my-deja.com> schrieb in im Newsbeitrag: s1ZZ5.146768$hD4.36926517_at_news1.rdc1.mi.home.com...
> 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 - 12:20:14 CET