I have a table with the following schema  :
where builddate and machinename form a primary key.

I want to create a row trigger which deletes old records , for a
machcine name or every insert ...so that only 100 records remain at
any point of time  for each machinename.

Any suggestions ?
SQL> desc buildstat;
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------

 PROJECTNAME                               NOT NULL VARCHAR2(100)
 SUCESS                                    NOT NULL VARCHAR2(50)
 LASTCHANGELIST                            NOT NULL NUMBER(38)
 LABEL                                              VARCHAR2(100)
 BUILDDATE                                 NOT NULL DATE
 LASTBUILD                                          DATE
 MACHINENAME                               NOT NULL VARCHAR2(50)
 BUILDTIME                                          VARCHAR2(38)

---

Clarification of Question by virajpurang-ga on 27 Jun 2003 11:09 PDT

I am looking at something like the following :

CREATE OR REPLACE TRIGGER DelTrigger
AFTER INSERT ON buildstat
FOR EACH ROW
BEGIN
//Get a count of all the records for a particular machine
//if count > 100, 
// delete next the old records so thar only 100 remain.
while (select count(*) from buildstat) > 100
 begin
   delete yourtable
     where builddate = (select min(builddate) from buildstat where
machinename = "machine name for which this record was inserted")
 end
END

However I am not sure if my syntax is right ...hence the question ...

---

Request for Question Clarification by mathtalk-ga on 28 Jun 2003 22:55 PDT

Hi, virajpurang-ga:

It seems to me that the closest approximation to accomplishing the
stated goal would be an "On Insert" trigger, which looks at the number
of rows being inserted and deletes beforehand the required number to
maintain COUNT(*) where machinename = ... at 100 or fewer rows.

Several objections might be raised to this, however, and the adequacy
of such an approach would surely be affected by the ways in which the
table is expected to be used.  For example, are rows updated in the
table (esp. can the machinename of a row be changed, according the
business logic of your application)?  Will rows be deleted by some
other process besides the trigger you want?

regards, mathtalk-ga

I don't have an exact answer for you, but you can try something like
this:

FOR EACH ROW
buildstat_count number(4);

BEGIN
select count(*) into buildstat_count from buildstat where machinename
= :old.machinename;
WHILE buildstate_count > 100 loop
   delete from buildstat_count where machine_name = :old.machinename
and builddate = (select min(builddate) from buildstat where
machinename = :old.machinename;
select count(*) into buildstat_count from buildstat where machinename
= :old.machinename;
end loop;
END

Thanks a lot guys ...
But I already coded ... something like 
which I call from a shell script.This shell script is added to the
system running Oracle as a cron job.

===============================================================================

DECLARE
	macname buildstat.machinename%TYPE;
	DATEBUILD buildstat.builddate%TYPE;
	CURSOR buildstat_cur IS 
	SELECT UNIQUE machinename FROM buildstat ORDER BY machinename;
 	BEGIN
	   FOR buildstat_rec IN buildstat_cur LOOP
	    macname := buildstat_rec.machinename;
        dbms_output.put_line('Machine name is '||macname);
		SELECT MIN(builddate) INTO DATEBUILD FROM 
		( SELECT builddate FROM buildstat WHERE machinename=macname ORDER BY
builddate DESC )A
		WHERE rownum < 102;
		dbms_output.put_line('Date is '||DATEBUILD);
		
		delete from buildstat where machinename=macname and builddate <
DATEBUILD;
		commit;
 	  END LOOP;
	  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('Did not fine any data '||macname);
END;
/
===============================================================================