Re: autoinc / counter Field in ORACLE

Hello,

First, the syntax is incorrect.There is no DML (update/insert/delete). Second, when it compiles, you will get a mutating table error when you insert a new
record. (you can't update a row that is being added to a table, the insert would loop
forever if it did allow such a thing)

The solution is to create a package or function or add the (real solution) "nextval"
into the statement that inserts new data.

i.e.
insert into table saleadmin.test
values
(SALEADMIN.SEQ_TEST.NEXTVAL,
 "the text field")
/

HTH.
//
cr OCP-DBA

"Peter Wilk" <PeterWilk_at_compuserve.com> wrote in message news:84o8lm$9fu$1_at_ssauraaa-i-1.production.compuserve.com...
> hello,
> I want to simulate an AUTOINC field in Oracle with a
> sequence number using the following code - however
> I always get a compile-error for the trigger.
> can anybody help me with that ?
> thanks a lot - peter
>
>
> DROP TABLE SALEADMIN.TEST CASCADE CONSTRAINTS;
>
> CREATE TABLE SALEADMIN.TEST
> (
> TESTID NUMBER(8,0) NOT NULL,
> CONSTRAINT PK_SUBNUMBERROAD PRIMARY KEY(TESTID),
> TEXT VARCHAR2(25) NOT NULL
> )
> TABLESPACE SALEDATA;
>
> DROP SEQUENCE SALEADMIN.SEQ_TEST;
> CREATE SEQUENCE SALEADMIN.SEQ_TEST
> INCREMENT BY 1 START WITH 1
> MAXVALUE 99999999 MINVALUE 1
> CYCLE CACHE 20 ORDER;
>
> CREATE OR REPLACE TRIGGER SALEADMIN.TRIG_TESTID
> BEFORE INSERT ON SALEADMIN.TEST
> BEGIN
> SALEADMIN.TEST.TESTID = SALEADMIN.SEQ_TEST.NEXTVAL;
> END;
>
>
> --
> Peter Wilk
> Assistance and Insurance Consulting
> http://ourworld.compuserve.com/homepages/PeterWilk
>
>
Received on Sun Jan 02 2000 - 15:40:42 CST