Re: How to ensure data consistency?
Date: 8 Sep 2004 08:57:22 -0700
Message-ID: <40f03cd5.0409080757.2e938024_at_posting.google.com>
"Felix E. Klee" <felix.klee_at_inka.de> wrote in message news:<20040908000514.7cbd418c.felix.klee_at_inka.de>...
[snip]
> What I still need, however, is a way to avoid "orphaned" entries in the
> specialization tables. Using my proposed data model, it would be
> possible to add such entries without having an entry in the main table.
> But, an entry in the main table cannot be made without an appropriate
> entry in the corresponding specialization table (foreign key
> constraints!).
>
> A solution for the latter problem, I guess, is to build a UI whose
> backend always creates rows for the specialization table *and* the main
> table.
>
> Felix
if your dbms allows you to create triggers on views one possible approach is to create appropriate view by joining super table with each of specialization tables and definning "instead of" trigger for update/delete/insert
or othher possible approach is to create stored procedure to enter appropriate values first into main table and then into specialization tables in one transaction
in both cases you should revoke permissions on both main table and spec tables
and allow permission only to view/stored procedure