Code Schema with Updatable Views

So how can you do a three schema architecture, and still present the Access Schema with something that looks like Tables? Remember that the Code Schema does not just contain stored procedures. It also contains views, and these views can be updatable. In other words, they can act just like tables. In Oracle, you can also create primary key and foreign key constraints on views, except that they must be specified as DISABLE NOVALIDATE. You would still want to have enabled constraints on your tables in the Data Schema. But by creating constraints on your views, you will inform the various code generators about the keys and relationships between your views. They will also inform the query optimizer, which MAY give you better query plans and better performance.

Inherently Updatable Views

So what is an updatable view? There are two ways to create an updatable view. The first way is the easiest, because some views are inherently updatable. For instance, any view that is a view of a single table, in which all not-null columns are included or defaulted is inherently updatable. A good example might be a table named PEOPLE, and a view of that table called EMPLOYEES, in which EMPLOYEES contains all of the columns of PEOPLE, and a WHERE clause that limits rows to those in which the IS_EMPLOYEE column equals ‘Y’. You might want to create that view with the “WITH CHECK OPTION” clause, so that you can’t update an EMPLOYEES row to set IS_EMPLOYEE to ‘N’.

You can also create updatable views as joins of more than one table as long as one of the tables is “key preserved”, and as before, the not-null columns must be defaulted or included in the view. Key preserved means that the view must never include more than one copy of the row. For instance, with tables in the HR schema, you could create a view like:

The EMPLOYEES table in this view is key-preserved because rows from this table only appear once in the view. The inclusion of the primary key of the table helps guarantee this. All of the not-null fields from EMPLOYEES are also included, but note that SALARY is not and need not be in the view since it is nullable. However, the DEPARTMENT_NAME in this view cannot be updated, since it doesn’t come from the key-preserved table. You can tell that a view is updatable and see the updatable columns in the view with a simple query in the data dictionary:

SELECT *
FROM user_updatable_columns
WHERE table_name = 'EMP_V';

Result:

OWNER

TABLE_NAME

COLUMN_NAME

UPDATABLE

INSERTABLE

DELETABLE

HR

EMP_V

EMPLOYEE_ID

YES

YES

YES

HR

EMP_V

FIRST_NAME

YES

YES

YES

HR

EMP_V

LAST_NAME

YES

YES

YES

HR

EMP_V

EMAIL

YES

YES

YES

HR

EMP_V

PHONE_NUMBER

YES

YES

YES

HR

EMP_V

HIRE_DATE

YES

YES

YES

HR

EMP_V

JOB_ID

YES

YES

YES

HR

EMP_V

DEPARTMENT_NAME

NO

NO

NO

HR

EMP_V

DEPARTMENT_ID

YES

YES

YES

So you probably want to expose the tables in the Data Schema only via updatable views in the Code Schema. Remember that one of the rules of this architecture is that the Access Schema NEVER has direct access to the Data Schema.

Updatable Views using INSTEAD OF Triggers

But what if the view that I want to use is not inherently updatable? Or I want to update the table that is not key-preserved? This is particularly true if the API that I want to use is not a Table API, but a Transactional API. For example, in the HR schema, I want to be able to insert new departments and their managers. This is fine with an inherently updatable view, as long as the manager already exists in the EMPLOYEES table. But my rule is: “Insert the new department into DEPARTMENTS, and if the manager exists, just set the MANAGER_ID field. But if the manager is not in EMPLOYEES, insert a row into that table, get its EMPLOYEE_ID, and use that for the MANAGER_ID.” Now, you could add this to the application in code there, but wouldn’t it be easier for your front end developers if they could just do one insert? This is simpler to use, and requires fewer round-trips to the database.

Fortunately, Oracle (and a few other RDBMSs) has a way to make ANY view updatable. You can write the view as you like, and then add INSTEAD OF triggers to define what happens upon INSERT, UPDATE and/or DELETE. An INSTEAD OF trigger does exactly what it says: it is PL/SQL code which is to execute instead of the insert, update or delete that was in the DML statement that ran against the view. This is powerful stuff, and as with any power, I believe the adage from Spider-Man: “with great power comes great responsibility”. Here are my rules for INSTEAD OF triggers:

If the trigger needs more than a few lines of code, call an API in a PL/SQL package.

At the very least, the trigger should appropriately change the tables used by the view, so that the current view row gets the expected changes.

Changes made by the trigger are part of the current transaction. Don’t call anything (except possibly logging) that starts an independent transaction.

If you are updating more than one table, always update in the same order. I always do details first, then master. This will help prevent deadlocks where two transactions are each waiting for resources held by the other.

Don’t do a COMMIT or ROLLBACK. Let the calling application do that.

Queue an event for the scheduler, if you want to trigger something like an e-mail. Event queues participate in the transaction so the e-mail won’t be queued unless the commit is done.

INSTEAD OF triggers can help with some of the problems that you will run into if you try to use triggers on the tables. For instance, if you need a master record to contain a running total from its details, this is doable with an INSTEAD OF trigger which can update both the master table and the detail table. But there are certainly pitfalls, such as the need to be aware of possible deadlocks. You need to think about what might happen if several users are trying to operate on the same rows at the same time.

I must emphasize that INSTEAD OF triggers operate within the scope of the current transaction. Any change they make to the underlying data is part of the same transaction, and all changes are either entirely committed, or entirely rolled back. This can work to your advantage – take the example of the funds transfer that I mentioned before. This is two INSERTs and either both are committed, or both are rolled back. But there are things that a trigger could do that are not part of the transaction, such as calls to UTL_FILE to write a file outside of the database. These happen even if the transaction is rolled back. Not only that, but there are cases when the code in a trigger might actually be run more than once. If you want to call one of the built-in packages with a name starting with “UTL”, you should probably queue an event to do it, rather than doing it directly. If you want to call one of the packages with a name starting with “DBMS”, you may be okay, but read the documentation carefully, and look for information that says the call requires a COMMIT.

Another warning about updatable views with INSTEAD OF triggers is that you cannot use the RETURNING clause. While you can have the trigger set a surrogate key from a SEQUENCE or with an identity column in Oracle 12c, it cannot be returned to the calling program. If your front end needs to know the key of the new row so it can set foreign keys for detail rows, you will need to query the sequence first to get the new key, and then set the key in the INSERT command. Also, SELECT … FOR UPDATE does not work on the tables of an updatable view that are not key-preserved. You may want to add a procedure to your API to lock rows that are about to be updated or deleted – but that means that your front end will need to call the lock procedure instead of the SELECT … FOR UPDATE.

Tomorrow, I'll give the first of several examples of using updatable views.