Example of Oracle "Instead of" Trigger

Question

Does anyone have a simple example that explains why one would want to use an Oracle instead of trigger ?

Answer 1

There are a few different scenarios I've seen

1) You build a system with an initial data model and provide a set of views for the applications to query that are just 1:1 mappings with the tables. In the future, if you decide that you need to alter the data model and break one table up into two different tables, you can do so without affecting any existing applications by keeping the view layer unchanged. But in order to keep things transparent to the applications, you have to continue to allow inserts on the view that cause inserts into two (or more) different tables, you need an INSTEAD OF trigger.

2) A variation on #1 where your applications start out just hitting tables directly. When you want to refactor a table definition without affecting existing applications, you rename the table (T becomes T_OLD), new tables are created, and a view called T is created with an INSTEAD OF trigger. This has the same effect-- it allows you to change the table declarations without requiring application changes.

Answer 2

The docs say they're for modifying views.

So if you create a view that's a JOIN of two tables, and you try to execute an INSERT statement on the VIEW, you can write an INSTEAD OF trigger that modifies the two underlying tables appropriately instead.

Answer 3

Justin has nailed the two main cases but there a couple of other uses.

One is where the table contains a historical trail of its data as well as the current version. Here we would want to build a view over the table (for instance to control which version is displayed) with an INSTEAD OF UPDATE trigger which updates the current record to be old and then applies the changes as an insert creating a new current record. There are variations on this theme; for instance, we can implement a policy of logical deletion by using an INSTEAD OF DELETE trigger to execute an update (setting a DELETED_FLAG column).

Another scenario is when the application requires us to maintain two copies of some data - a master table and a local table. We would use an updateable view to select from the appropriate table and an INSTEAD OF trigger to decide which table to apply DML to.

These are not glorious examples of how to design a good, sound data model. But they are the sorts of kludges we occasionally find ourselves having to implement.

Answer 4

Another model, which I've seen used very successfully, is when the application architecture wants to insulate the database design from the front end. And they want to do that on the database side, rather than through an object-relational framework.

For example, if you want to make the job of the front-end developer very easy, you can build a view for each front end page (or set of pages). Then use instead-of triggers to deal with the mapping back to the underlying tables.

Of course, it helps to have solid database developers who understand how Oracle works.