Re: autoinc / counter Field in ORACLE

A copy of this was sent to "crumedgeon" <zimsbait_at_hotmail.com> (if that email address didn't require changing) On Sun, 2 Jan 2000 16:40:42 -0500, you wrote:

>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)
>

you can easily update a row being added/update to/in a table. simply code:

create or replace trigger my_trigger
before insert on T for each row
begin

   select my_seq.nextval into :new.column_to_get_sequence from dual; end;

You can easily modify any column of a table being inserted/updated using the :new record in a BEFORE FOR EACH ROW trigger.

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

that is one solution yes -- the trigger works as well though...

>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
>>
>>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jan 02 2000 - 19:21:51 CST