Re: How to ensure data consistency?

"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

all logic remains in DBMS which is AFAIK more reliable solution

strider5 Received on Wed Sep 08 2004 - 10:57:22 CDT