Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Creating record versions with trigger

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    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
    ....
    ....
    ....

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •