Re: Autonumber. MUTATING TABLES AND RECURSIVE TRIGGERS.

You cannot touch a mutating table.

You also have an infinitely recursive trigger. Your trigger is fired before insertions to the table, and in the trigger body you are inserting into the same table which should fire the same trigger!

What you should have in your trigger is something that looks like the following:

CREATE TRIGGER COUNTER_INCREMENT
BEFORE INSERT ON SAMPLE_TABLE
FOR EACH ROW
BEGIN
  :NEW.SAMPLE_COUNTER = SAMPLE_AUTONUMBER.NEXTVAL; END; Notice your are setting up the correct new value of SAMPLE_COUNTER which will be inserted by the statement that fired the trigger.

Good luck.

Smiths9312 wrote in message
<19980927134547.05884.00002446_at_ng-fd1.aol.com>...

I am designing a database with an Access front-end and Oracle tables. Recently I have run into a problem with an autonumber field. I have researched
my Oracle book and the following is the code I think will do the job. Could
someone please look over it to make sure it is correct

To create a sequence number:

CREATE SEQUENCE SAMPLE_AUTONUMBER
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 20; A trigger that inserts the sequence number into the Counter field.

CREATE TRIGGER COUNTER_INCREMENT
BEFORE INSERT ON SAMPLE_TABLE
FOR EACH ROW
BEGIN
INSERT INTO SAMPLE_TABLE (SAMPLE_COUNTER) VALUES (SAMPLE_AUTONUMBER_NEXTVAL);
END; For some reason the trigger code doesn't do anything when it is entered. SQL
acts like there should be more code and doesn't run it. Received on Mon Sep 28 1998 - 02:10:34 CEST