Re: Backupping the table values

On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb(at)gmail(dot)com> 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(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org