Where will the record get inserted first?

Question

I have a schema called "CUSTOMERS". In this schema there is a table called RECEIVABLES.

There is another schema called "ACCOUNTS". In this schema, there is a table called RECEIVABLES_AC.

RECEIVABLES_AC has a public synoym called RECEIVABLES. The table structure of both the tables is exactly the same.

If your front-end uses the customer schema credentials to establish a connection, how can you ensure that the record will get inserted in RECEIVABLES_AC without changing the front-end code.

I think this is a trick question. Short of renaming the table RECEIVABLES in the CUSTOMERS schema, I don't see how this can be done.

Answer 1

What isn't specified is if the behavior is supposed to be instead-of or in-addition-to.

1. Use replication on ACCOUNTS.RECEIVABLES to propagate DML to CUSTOMER.RECEIVABLES_AC. Triggers, streams, what have you.

2. Use the ALTER SESSION SET CURRENT_SCHEMA statement to change the default namespace of the user's session.

The right way to respond is to fix the design, and to not have multiple receivables tables with public schemas floating about.

Answer 2

The only way that I can think of (without changing the login or insert statement) is to use a database trigger that runs on login and changes the current schema to ACCOUNTS:

create or replace trigger logon_set_schema
AFTER LOGON ON DATABASE
BEGIN
   if sys_context('USERENV','SESSION_USER') = 'CUSTOMERS' then
      execute immediate 'alter session set current_schema=accounts';
   end if
END;
/

However, this would likely break other aspects of the code, so changing the application to specify the schema name would be vastly preferable.

Answer 3

Two good ways to solve this problem are:

Option 1

1. Rename CUSTOMERS.RECEIVABLES.
2. Drop the public synonym.
3. Create a private synonym in the CUSTOMERS schema, called RECEIVABLES that points to ACCOUNTS.RECEIVABLES_AC.

Option 2

1. Change the front-end to refer to RECEIVABLES_AC instead of RECEIVABLES.
2. Create a private synonym in the CUSTOMERS schema, called RECEIVABLES_AC that points to ACCOUNTS.RECEIVABLES_AC.

I would prefer Option 2. Private synonyms are a great way of controlling which tables are used by a particular schema, without having to hard-code the schema name in the app.