Oracle Example "Instead of" Trigger "

Does anyone have a simple example explaining why one would need to use Oracle instead of a trigger ?

+2


source to share


4 answers


There are several different scenarios I've seen

1) You create a system with an original data model and provide a set of views for application queries that are just 1: 1 mappings to tables. In the future, if you decide that you need to change the data model and split one table into two different tables, you can do so without affecting any existing applications, without changing the presentation layer. But in order to make things transparent to applications, you must continue to allow inserts in the view that cause inserts to two (or more) different tables, you need an INSTEAD OF trigger.



2) Option number 1, in which your applications are launched directly, directly falling into the tables. If you want to reorganize a table definition without affecting existing applications, you rename the table (T becomes T_OLD), new tables are created, and a view named T is created using the INSTEAD OF trigger. This has the same effect as allowing table declarations to be changed without requiring application changes.

+8


source


Justin has attached two main cases , but there are several other uses out there.

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



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

These are not great examples of how to create a good, sound data model. But they are varieties of kludges that we sometimes find ourselves forced to implement.

+4


source


The docs say they are for changing views.

So, if you are creating a JOIN view of two tables and you are trying to INSERT the VIEW, you can write an INSTEAD OF trigger that updates the two base tables accordingly.

+2


source


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

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

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

+2


source







All Articles