How to maintain a table of largest entries in another table

From: Karsten Spang <krs_at_kampsax.dk>
Date: 1995/10/14
Message-ID: <45osaj$kq6_at_stamlink.kampsax.dk>#1/1

  Hi

I have a problem:
I want to have a table with the "latest" entry in another table.

The situation is as follows: A number of "stations" collect various kinds of data automatically. The data are transmitted to a central location, and inserted into a database. There are a number of tables, depending on the kind of data the station is measuring. All these tables have a composite key consisting of the station ID and a timestamp. The time stamp is generated by the station, it is not the time the data are recieved or entered into the database. Data may be modified manually if errors are found.

Now I want to have a table over the times of the "latest" entries for each station, i.e. those with the highest time stamp.

My first thought was something like:
CREATE OR REPLACE TRIGGER ins_trig
AFTER INSERT ON stationstype1
FOR EACH ROW
DECLARE
        latest_stamp DATE;
BEGIN

	SELECT stamp INTO latest_stamp FROM newest_stamps WHERE
		newest_stamps.ident = :new.ident;
	IF latest_stamp < :new.stamp THEN
		UPDATE newest_stamps SET newest_stamps = :new.stamp
			WHERE newest_stamps.ident = :new.ident;


	/* So far OK */

EXCEPTION
WHEN NO_DATA_FOUND THEN

	INSERT INTO newest_stamps (ident,stamp) 
		VALUES(:new.ident,:new.stamp);


	/* This conflicts with the rules about constraing tables,


           ident is the primary key of newest_stamps. */

END; CREATE OR REPLACE TRIGGER del_trig
AFTER DELETE ON stationstype1
FOR EACH ROW
DECLARE
        latest_stamp DATE;
BEGIN

	BEGIN
		SELECT stamp INTO latest_stamp FROM newest_stamps WHERE
			newest_stamps.ident = :old.ident;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			latest_stamp := :old.stamp;
	END;

	BEGIN
		IF latest_stamp = :old.stamp THEN
			/* Find the newest one left */
			SELECT MAX(stamp) INTO latest_stamp FROM stationstype1
				WHERE ident = :old.ident;
			/* Bad, can't read the mutating table */
			BEGIN
				UPDATE newest_stamps 
					SET newest_stamps.stamp = latest_stamp
					WHERE newest_stamps.ident = :old.ident;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					INSERT INTO newest_stamps (ident,stamp) 
					VALUES(:old.ident,latest_stamp);
					/* Same problem as above */
			END;
		ENDIF;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			BEGIN
				DELETE FROM newest_stamps
					WHERE ident = :old.ident;
					/* Ditto */
			EXCEPTION
				WHEN NO_DATA_FOUND;
			END;
	END;

END; and a combination of the above for update.

Why can't I use a view? First thing: it is to be selected from several tables (*not* a join). Second thing: I want to have a trigger on the "latest" table, to make an application read it each time it is updated, and then fetch the latest data from the other tables for stations which have been updated. (A view cannot have a trigger.)

                          Thanks, 

			Karsten.

-- 
Karsten Spang                                    Snail Mail:  Kampsax Data
E-mail: krs_at_kampsax.dk                           P.O. Box 1142
Phone:  +45 36 39 07 88                          DK-2650 Hvidovre
Fax:    +45 36 77 03 01                          Denmark

Received on Sat Oct 14 1995 - 00:00:00 CET