Home » SQL & PL/SQL » SQL & PL/SQL » Trigger on a View
Trigger on a View [message #240325] Thu, 24 May 2007 11:04 Go to next message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

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, 
       bh.batch_status <++ THIS ONE HERE!
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;
   insert into enda_debug (c,d) values ('Trigger Fired!', :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!

[Updated on: Thu, 24 May 2007 11:06]

Report message to a moderator

Re: Trigger on a View [message #240338 is a reply to message #240325] Thu, 24 May 2007 11:35 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
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



y you not start fine grained auditing on your base tables inorder to caputer DMLs??
Re: Trigger on a View [message #240346 is a reply to message #240325] Thu, 24 May 2007 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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

What is your goal?
When a column is modified in a base table, it is modified in the view as the view is just a query on the base table.

Regards
Michel

[Updated on: Thu, 24 May 2007 11:49]

Report message to a moderator

Re: Trigger on a View [message #240360 is a reply to message #240346] Thu, 24 May 2007 12:22 Go to previous messageGo to next message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

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 Query
Is it possible to have the Trigger on the View rather than Table B which detects the row update in Table B.

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

I hope this is clearer!

[Updated on: Thu, 24 May 2007 12:36]

Report message to a moderator

Re: Trigger on a View [message #240364 is a reply to message #240360] Thu, 24 May 2007 12:28 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Yes it is possible.
take a look.


http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm
Re: Trigger on a View [message #240369 is a reply to message #240364] Thu, 24 May 2007 12:39 Go to previous messageGo to next message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

Thanks for the reply.

Do you mean INSTEAD OF Triggers.

This is not quite what I'm looking for since the Update is not taking place on the view and in any case even though the View contains a Join in this case the View is Inherently Updateable.

If you're referring to something else in the link then let me know what particular section.

Actually thinking more about it I think I'll have to define the trigger on the Base Table since a View is just an Query that has no persistence.

[Updated on: Thu, 24 May 2007 12:41]

Report message to a moderator

Re: Trigger on a View [message #240370 is a reply to message #240369] Thu, 24 May 2007 12:41 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
did you take a look at that documentation.
Re: Trigger on a View [message #240371 is a reply to message #240360] Thu, 24 May 2007 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't understand.
A view is just a (stored) query, it is virtual.
Anything you do on a base table is seen in the view.

Regards
Michel
Re: Trigger on a View [message #240375 is a reply to message #240371] Thu, 24 May 2007 12:48 Go to previous messageGo to next message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

Michel Cadot wrote on Thu, 24 May 2007 13:41
I still don't understand.
A view is just a (stored) query, it is virtual.
Anything you do on a base table is seen in the view.

Regards
Michel



Have you read the 4th thread where I restate the problem. I can't be any more specific than that Smile I'm trying to avoid placing the Query on the Base Table but place it on the View instead.

Even though a View is Virtual you can still define Triggers on them though.

[Updated on: Thu, 24 May 2007 12:52]

Report message to a moderator

Re: Trigger on a View [message #240376 is a reply to message #240370] Thu, 24 May 2007 12:49 Go to previous messageGo to next message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

DreamzZ wrote on Thu, 24 May 2007 13:41
did you take a look at that documentation.


Hi DreamzZ,

I did thanks. All I can guess is that you were referring to INSTEAD OF Triggers which is not the issue here.

I don't see anything else that would help. Are you referring to a particular section?

[Updated on: Thu, 24 May 2007 12:52]

Report message to a moderator

Re: Trigger on a View [message #240377 is a reply to message #240375] Thu, 24 May 2007 12:55 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
more specific

http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1169
Re: Trigger on a View [message #240378 is a reply to message #240325] Thu, 24 May 2007 12:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I'm still not clear as to what/why you want.

Is it that you just want to track when an update is done via the view vs. via the table? That is the only thing I can think of because if you update via the view, the table sees it and if you update via the table, the view sees it.
Re: Trigger on a View [message #240379 is a reply to message #240376] Thu, 24 May 2007 12:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What you want is not possible through a view. The actual DML is not done through the view, so triggers on the view will not fire.

Think about it. Allowing this would mean that for each DML on each and every table, a check needs to be done whether this table is in a (nested?!) view with such a trigger!
This would kill performance.

[Updated on: Thu, 24 May 2007 12:59]

Report message to a moderator

Re: Trigger on a View [message #240380 is a reply to message #240378] Thu, 24 May 2007 12:58 Go to previous messageGo to next message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

joy_division wrote on Thu, 24 May 2007 13:57
I'm still not clear as to what/why you want.

Is it that you just want to track when an update is done via the view vs. via the table? That is the only thing I can think of because if you update via the view, the table sees it and if you update via the table, the view sees it.


Exactly. I want to track the changes via the View rather than Via the Table.
Re: Trigger on a View [message #240381 is a reply to message #240379] Thu, 24 May 2007 13:00 Go to previous message
maddene
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

Frank wrote on Thu, 24 May 2007 13:58
What you want is not possible through a view. The actual DML is not done through the view, so triggers on the view will not fire.


Yep, I've spent a couple of hours investigating and you're right. I'll just create the Trigger on the base table.

I thought for about 10 seconds of using a Materialized View.

Thanks for everyones help!

[Updated on: Thu, 24 May 2007 13:03]

Report message to a moderator

Previous Topic: CONNECTIN TO ACUCOBOL VIA ODBC
Next Topic: How to count records from a population?
Goto Forum:
  


Current Time: Sat Dec 10 18:12:38 CST 2016

Total time taken to generate the page: 0.07673 seconds