Re: Trigger in Oracle 7.3

Hi Clemens,

we had a similar problem: We have a table BANKNUMBERS containing all the bank numbers which are allowed. This table is central in a schema called COMMON. All the account tables ACCINFO stored in different other schemas (USER1.ACCINFO, USER2.ACCINFO, ...) contain bank numbers which must be in COMMON.BANKNUMBERS.

Our solution was to define foreign key constraints on all the tables USERx.ACCINFO references COMMON.BANKNUMBERS. This works very well under the condition that you grant an object right (I think it is REFERENTIAL CONSTRAINT or similar) on COMMON.BANKNUMBERS to USERx.

Martin

Clemens Hoffmann wrote:

> Hi there,
>
> we have two database schema that cannot be merged but whose
> contents has to be in sync for some tables in each schema. Some
> tables refer records in tables that have to be in sync. Some
> references are from tables that are local to a schema. A record
> can only be deleted when no record in another table refers to
> the record
>
> We have solved the situation in using database trigger that
> handle the data syncronisation. We classified the trigger
> to be able to create the triggers from seperate trigger tables
> we manage ourself. For our solution we have two opinions:
>
> - Use one trigger for each table that handles insert, update and
> delete for the data sync and the referenc counting on refered
> data record.
>
> - Use one trigger for each operation which means six
> trigger for each table.
>
> Does anybody has experience which is more performant
> or if one of the solution may create problems. We have to
> hold 18 tables in sync.
>
> Greetings
>
> Clemens
Received on Mon May 24 1999 - 16:33:48 CDT