Triggers on Views fire for table? 
Author Message
 Triggers on Views fire for table?

I have table t_table with view v_view on it (create view v_view as select *
from t_table;)

What I want is to place a trigger on the view v_view, so that when an insert
or upate is done into t_table the trigger fires. Is this possible? The
reason I ask is because I only have access to the v_view, and I don't even
know (or have rights) to what table it's coming from.

When I try placing a trigger on v_view, it only fires if an insert/update is
done "through" the view itself, but not the table. Is this the only way
triggers on views work?

--
Luch
replace nospam with usa for e-mail.



Sat, 22 May 2004 11:45:13 GMT
 Triggers on Views fire for table?


says...

Quote:

>I have table t_table with view v_view on it (create view v_view as select *
>from t_table;)

>What I want is to place a trigger on the view v_view, so that when an insert
>or upate is done into t_table the trigger fires. Is this possible? The
>reason I ask is because I only have access to the v_view, and I don't even
>know (or have rights) to what table it's coming from.

>When I try placing a trigger on v_view, it only fires if an insert/update is
>done "through" the view itself, but not the table. Is this the only way
>triggers on views work?

yes, triggers only fire on the object upon which they are defined.

A trigger on a view fires if and only if you insert/update/delete the view.
Triggers on tables only fire when you modify the table.

Quote:
>--
>Luch
>replace nospam with usa for e-mail.

--

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sat, 22 May 2004 20:48:48 GMT
 Triggers on Views fire for table?
Anybody have ideas how to get around or a good way to deal with this. I was
given only select access to a certain view, I don't even know what table
it's coming from. I want to process each record as it comes into the view
(just like an insert trigger on a table).

--
Luch
replace nospam with usa for e-mail.


Quote:

"Luch"
> says...

> >I have table t_table with view v_view on it (create view v_view as select
*
> >from t_table;)

> >What I want is to place a trigger on the view v_view, so that when an
insert
> >or upate is done into t_table the trigger fires. Is this possible? The
> >reason I ask is because I only have access to the v_view, and I don't
even
> >know (or have rights) to what table it's coming from.

> >When I try placing a trigger on v_view, it only fires if an insert/update
is
> >done "through" the view itself, but not the table. Is this the only way
> >triggers on views work?

> yes, triggers only fire on the object upon which they are defined.

> A trigger on a view fires if and only if you insert/update/delete the
view.
> Triggers on tables only fire when you modify the table.

> >--
> >Luch
> >replace nospam with usa for e-mail.

> --

> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp



Sun, 23 May 2004 09:59:53 GMT
 Triggers on Views fire for table?

Quote:

> Anybody have ideas how to get around or a good way to deal with this. I was
> given only select access to a certain view, I don't even know what table
> it's coming from. I want to process each record as it comes into the view
> (just like an insert trigger on a table).

> --
> Luch
> replace nospam with usa for e-mail.

Each record only "comes into the view" when you access the view.

Are you then modifying the records or just doing something like updating
an on-screen display? You may have to set up a periodic process to
monitor the view and process new records as you see them.

If you are going to modify these records then actually the correct
solution is to get the trigger set on the real table (or tables). If you
code the trigger properly, you should be able to lobby the DBA to
install it. If it is really necessary for your project, then your
manager should be able to support your request.

--
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com



Sun, 23 May 2004 21:11:09 GMT
 Triggers on Views fire for table?
Basically I wanted to take the data being inserted into the view (whatever
is in the :new variables) and process it, then insert/move it to other
tables. If I understand correctly that the record only comes into the view
when the view is queried, I suppose the type of trigger I wanted to create
isn't possible.

The DBA will only give me access to the view, so I'll probably have to go
with a timed process that periodically queries the view like you mention.

thank you both for your help

--
Luch
replace nospam with usa for e-mail.


Quote:


> > Anybody have ideas how to get around or a good way to deal with this. I
was
> > given only select access to a certain view, I don't even know what table
> > it's coming from. I want to process each record as it comes into the
view
> > (just like an insert trigger on a table).

> > --
> > Luch
> > replace nospam with usa for e-mail.

> Each record only "comes into the view" when you access the view.

> Are you then modifying the records or just doing something like updating
> an on-screen display? You may have to set up a periodic process to
> monitor the view and process new records as you see them.

> If you are going to modify these records then actually the correct
> solution is to get the trigger set on the real table (or tables). If you
> code the trigger properly, you should be able to lobby the DBA to
> install it. If it is really necessary for your project, then your
> manager should be able to support your request.

> --
> Edward J. Prochak   --- Magic Interface, Ltd.
> Ofc: 440-498-3700
> on the web at       --- http://www.magicinterface.com




Thu, 27 May 2004 00:12:52 GMT
 Triggers on Views fire for table?



Quote:
> I have table t_table with view v_view on it (create view v_view as select
*
> from t_table;)

> What I want is to place a trigger on the view v_view, so that when an
insert
> or upate is done into t_table the trigger fires. Is this possible? The
> reason I ask is because I only have access to the v_view, and I don't even
> know (or have rights) to what table it's coming from.

> When I try placing a trigger on v_view, it only fires if an insert/update
is
> done "through" the view itself, but not the table. Is this the only way
> triggers on views work?

The trigger fires when an event on the object they are based occurs, not
when en event on some other object....
The view is unaware of the changes on the table untill it is queryied.

Giovanni Bronzini

Quote:

> --
> Luch
> replace nospam with usa for e-mail.



Fri, 28 May 2004 07:49:28 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. INSTEAD OF triggers on View not firing with DTS

2. Instead of Trigger on View failing to fire

3. Firing Triggers in Oracle to Sybase from Sybase point of view

4. To fire the same trigger on 2 tables

5. can dts import fire triggers on tables with identity columns

6. Update Trigger fires when nothing is updated on SQL 7.0 table

7. Inserted table is an invalid object when trigger statement fires from exec

8. Trigger fires only if value from other table is true

9. trigger on table for insert never fire !!!

10. My update trigger on subscriber table is firing twice

11. Log Shipping - Do triggers on tables fire?

12. HELP!: Trigger won't fire against remote table


 
Powered by phpBB® Forum Software