Oracle Database - Triggers on a View

cousin_borat

I know it is possible to create a trigger on a view but my question relates to how the triggers work with views.

Is it possible to configure a trigger on a view that detects a column change in the corresponding underlying base table column? Rather than the behaviour I'm observing where a View Trigger can only detect direct updates on the view? (The view defined in this case is inherently updatable).

It may be easier if I give the example and add comments.

I have the following view defined. I would like to detect changes to the column highlighted in the code section and perform an UPDATE on an underlying base table based on the value the batch_status column highlighted is changed to.

The main point is that the update to the column is on the underlying base table NOT to the corresponding column in the view.

CREATE OR REPLACE VIEW bio_gme_cm_iface_batch_v AS
SELECT br.biogen_partner_code, 
       br.batch_no, 
       br.item_no, 
       br.status, 
       [B]bh.batch_status <++ THIS ONE HERE![/B]
FROM   bio_in_gme_batch_release br, gme_batch_header bh
WHERE  br.batch_no = bh.batch_no;

My question is whether I can define a trigger on this view that will detect the change on the column in the underlying base table. I'm thinking not since I have defined a trigger as follows on the view but it does not fire after updates on the base table column. Here is the trigger defined on the view in any case

create or replace trigger bio_gme_cm_iface_batch_v_t
instead of update
on bio_gme_cm_iface_batch_v
for each row
begin
   update bio_in_gme_batch_release
   set	  status = 'S'   
   where  batch_no = :old.batch_no;   
end; 

My preference is not to have to create triggers on base tables if atall possible but in the end may have to bite the bullet.

Or if anyone has alternative suggestions.

Thanks!

bonkey

YOu might have to end up putting it on the table....but...

Views containing joins aren't updateable in Oracle. However, check out "INSTEAD OF" triggers....they might do what you want.

http://www.devx.com/tips/Tip/21433

Surely you'd want your trigged to fire if the data was updated seperately to the view, though?

My preference is not to have to create triggers on base tables if atall possible but in the end may have to bite the bullet.

As a matter of interest...why do you think it would be a better idea to have them on views than on tables. I know there are situations where it can be desireable (because you want to limit the trigger to certain types of upates, for example), but I'm curious as to what you're reasoning is.

cousin_borat

Apologies for the Verbosity of my first post! I'll try and be more succinct this time.

My View is made up of columns from two Tables A and B

Scenario
Row Update takes place in Table B changing column B1.
B1 is also a column in the View.

Result
The Trigger fires updating Table A with a value X.

My Question
Is it possible to have the Trigger on the View rather than Table B which detect the row update in Table B.

Remember the update is being performed on Table B not directly on the View.

cousin_borat

Hey Bonkey,

bonkey wrote:

Views containing joins aren't updateable in Oracle. However, check out "INSTEAD OF" triggers....they might do what you want.

You're right, for the most part views with joins aren't updateable. However this is one of the rare cases where it is.

I hope my new post makes it clearer. The issue is that I'm not actually updating the View directly.

bonkey wrote:

As a matter of interest...why do you think it would be a better idea to have them on views than on tables. I know there are situations where it can be desireable (because you want to limit the trigger to certain types of upates, for example), but I'm curious as to what you're reasoning is.

In the case here the Base Tables contains information pertaining to many customers. The View queries a subset of these customers. Therefore it is preferable to have the Trigger/Alert on the View so I would avoid having to hardcode filtering in the Trigger which I find shaky enough at the best of times

cousin_borat

After spending a couple of hours researching and testing this I've come to the conclusion it is not possible in Oracle 9i.

It does make sense I guess since the actual DML is not done through the View so the Trigger on the View won't fire.

bonkey

cousin_borat wrote:

You're right, for the most part views with joins aren't updateable. However this is one of the rare cases where it is.

Oh, right. I learn something new every day. Cool

In the case here the Base Tables contains information pertaining to many customers. The View queries a subset of these customers. Therefore it is preferable to have the Trigger/Alert on the View so I would avoid having to hardcode filtering in the Trigger which I find shaky enough at the best of times

Ahhh. I see.

At the very least, rather than getting to trigger to refilter same as the view (thus having your filter coded twice, and prone to desynchronisation if you ever update the code in one and forget the other), you can...at least...reference the view from the trigger as your filter mechanism.

cousin_borat

I'm actually going to do away with the view altogether. Along the lines of your reasoning. I want to have as few custom objects in the database as possible.

Actually alot of the basic Database Queries are in the Controller Layer coded in Hibernate HQL to reduce the number of stored procedures in the database as well.