On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb@xxxxxxxxx> wrote: > I am allowing my users to delete data from certain tables. However, to > be able to do a rollback of user changes, I decided to create another > "backup" schema that most data tables will copy data to, upon delete. > > So basically what I have is: > > public schema, in which there are two tables, A and B. > backup schema, in which there are two tables, A and B. > > On table A and B in public I add a trigger "On Delete" which inserts > the deleted data to the matching tables in the backup scehma. > > That'd work fine except the foreign keys problem. In A I have a > column, "B_id" that is a foreign key to an "id" in the B table. Thus > it means that I have to delete from A before I delete from B. If you want to a DELETE on table B in public to cause the dependant (child) rows on public A to be removed you can make the foreign key cascade with "ON DELETE CASCADE". This will guard against orphaned rows in B. If both A and B have the ON DELETE trigger a DELETE on A will cause the correct DELETE on B, and the insertion order in the backup schema should be correct. That is, if you really need the foreign keys on the backup schema at all. If the backup is only written to by triggers the foreign keys may be a waste. -- Mike Rylander mrylander@xxxxxxxxx GPLS -- PINES Development Database Developer http://open-ils.org ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
