Looking for some advice:

I have a 2 tables that I need to create a history of the last 5 versions of the records in those tables. Not every record gets updated at the same time, so some records may have 5 versions, others may have 1. I've created a trigger which fires based on an update to a field in another table.

The trigger then runs about 12 insert and delete statements, which basically move the records of the server which was updated up 1 version, and deletes the previous records before an insert.

I've tested this and it works, but I'm concerned this is not the most effective way to do this, and as the tables grow in size, there may be some delay to select the table and insert to the next. Are triggers the most effective way to do this? Are stored procedures better? any ideas?

Thanks,
Charlie

You should be able to do it with one delete and one insert statement. How are you "versioning" the rows? Also note that you need to have an insert trigger to capture versions that way also.

Andy

I've created 5 seperate tables for the version which all have the same structure.

Tablev1, Tablev2, Tablev3, Tablev4, Tablev5

On an UPDATE to the flag Table, my trigger fires which

Deletes X Server records from Tablev5
Inserts X Server records from Tablev4 in v5
Deletes X Server records from Tablev4
Inserts X Server records from Tablev3 in v4
....
....
....

That seems like an awfully complicated way to do the versioning. Why not have just one table that has a timestamp of when the changed occurred.

Then your insert and update triggers would look like this.

BEGIN
DECLARE num_versions int;
DECLARE min_version_timestamp timestamp;

set (num_versions,min_version_timestamp) = SELECT COUNT(*),min(version_timestamp) from version_table where .... ;

IF (num_versions >= 5)
THEN delete from version_table where version_timestamp <= min_version_timestamp;
END IF;

INSERT into version_table (version_timestamp,...) values (current timestamp, ...);

END;


Andy