Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: FIFO table question

Re: FIFO table question

From: Tina Tran <tinat_at_sonica.com>
Date: 1997/10/16
Message-ID: <01bcd9d0$0d081630$2aaeb6cc@dino>#1/1

Gee whiz, if you provide a solution, please verify it first before the poor folk tried them out.
This solution will not work since you will encounter oracle 4091 mutating constraint error.
One way to approach it is to have a package which contains procedure to insert the data
into the table. After insert, it can check for the row count and delete the desire record.

Leigh Randall <randall_leigh_at_bah.com> wrote in article <344510BD.AA831DAA_at_bah.com>...
> Alex Tait wrote:
> >
> > Is it possible to set up a table whereby, once a predefined number of
> > rows
> > has been added, the first row is deleted?
> > There are no relations to the table, it's simply an event log.
> > --
> > Alex Tait
> > Force12 Solutions Ltd
> > ajt_at_force12.com
>
> Well, basically what I would do is put some kind of counter on the
> record (using a sequence is best) and then have a post-insert trigger on
> the table that checks the count on the table. If it exceeds a specific
> value then it selects the minimum value of the counter as a start and
> deletes however many records it needs to drop below the value.
>
> create table event_log
> (event_counter number(6) CONSTRAINT pk_event PRIMARY KEY,
> event_date date NOT NULL,
> event_description varchar2(255) NOT NULL);
>
> create sequence event_sequence;
>
> create or replace trigger remove_rows
> after insert on event_log
> declare
> num_records integer;
> lowest_number number(6);
> begin
> /* First check to see how many rows are already in the table */
> select count(*) into num_records
> from event_log;
> if num_records > 500 then /* or whatever your target value is */
> select min(event_counter) into lowest_number
> from event_log;
> begin
> delete from event_log
> where event_counter = lowest_number;
> exception
> when NO_DATA_FOUND then
> null;
> end;
> end if;
> end;
>
> You could also put a pre-insert trigger on the event_log table to set
> the event_counter to the NEXTVAL of the sequence.
>
Received on Thu Oct 16 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US