Re: Future of Triggers

From: <oratune_at_aol.com>
Date: Fri, 27 Oct 2000 14:38:33 GMT
Message-ID: <8tc418$llf$1@nnrp1.deja.com>

In article <1mfK5.9116$SF5.177933_at_ozemail.com.au>,   "ozi" <spuzic_at_camtech.net.au> wrote:
> Hi Everyone,
>
> Does anyone know if ORACLE are planning to introduce triggers for
 views. For
> example you would be able to set a trigger on the following SQL
 statement;
>
> SELECT * FROM EMP_TABLE
> WHERE EMP_SAL > 20000
>
> and whenever a row is inserted that matches this query, a trigger is
 fired.
>
> Cheers,
> Tom
>
>

Oracle already has something of that nature -- they're called INSTEAD OF triggers and they are placed on views to allow inserts, updates and deletes on the underlying tables of a view. Per the Oracle documentation:

---

INSTEAD OF Triggers

---

Note:
INSTEAD OF triggers are available only if you have purchased the Oracle8i Enterprise Edition. They can be used with relational views and object views. See Getting to Know Oracle8i for information about the features available in Oracle8i Enterprise Edition.

---

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

Modifying Views

Modifying views can have ambiguous results:

• Deleting a row in a view could either mean deleting it from the base table or updating some column values so that it will no longer be selected by the view.
• Inserting a row in a view could either mean inserting a new row into the base table or updating an existing row so that it will be projected by the view.
• Updating a column in a view that involves joins might change the semantics of other columns that are not projected by the view.

Object views present additional problems. For example, a key use of object views is to represent master/detail relationships. This operation inevitably involves joins, but modifying joins is inherently ambiguous.

As a result of these ambiguities, there are many restrictions on which views are modifiable (see the next section). An INSTEAD OF trigger can be used on object views as well as relational views that are not otherwise modifiable.

Even if the view is inherently modifiable, you might want to perform validations on the values being inserted, updated or deleted. INSTEAD OF triggers can also be used in this case. Here the trigger code would perform the validation on the rows being modified and if valid, propagate the changes to the underlying tables.

INSTEAD OF triggers also enable you to modify object view instances on the client-side through OCI. To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is inherently modifiable. However, it is not necessary to define these triggers for just pinning and reading the view object in the object cache.

---

So, maybe this is what you are looking for.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Oct 27 2000 - 09:38:33 CDT