Create Backup of Record
I am not sure if what I want is feasible, so I thought I'd give it a try here.
I have a database of records connecting to a number of episodes of a television show. The database is broken down in tables based on the the portions of the show. The tables go SHOW->EPISODE->MEDIA. What I would like to do is create a method in which if I modify a particular field it will create a backup of the previous version of a record in the media table.
Allow me to elborate. In the MEDIA table there is a number field named "version." I want it so that if I change the version number in that field from say 1 to 2, the record is copied so I can view a previous version of the record. Ideally, I would have this happen in a new table, so all previous versions are the child of the main record and do not clutter the main MEDIA table. With that in mind, I created a child table of the MEDIA table named MEDIA_PREVIOUS. I would assume that it would be executed through a script trigger, but I am not sure exactly how to do this. Any help or direction would be greatly appreciated.