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?