Re: FIFO table question

From: Leigh Randall <randall_leigh_at_bah.com>
Date: 1997/10/15
Message-ID: <344510BD.AA831DAA@bah.com>#1/1

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 Wed Oct 15 1997 - 00:00:00 CDT