I have a question about the trigger on view.
I wanna create a view which will join a lot of tables in oracle db.
After that, I wanna create a trigger which will be fired whenever
the view has been updated/inserted/deleted. I know it should work
create view table_views ......(from table1, table2, table3,...etc)
create trigger view_trigger instead of update on table_views ......
however, is it possible I can only update one of tables, and fire the trigger? (That means : update table1 .......etc)
Or any other solution?
I can not build any trigger on those tables, the only thing I can use is
the view created, the result I wanted is when there are any change made which will affect the view query result, I would like to fire some
sql block. What should I do for that?
Thanks in advance.
If you want some action to be triggered when table1 is updated directly, then the only way to do that would be with a trigger on table1 - which you say you cannot do (why?).
If you want to force users to update via the view, you can simply revoke their update privileges on the base tables. You could then even have some additional views with a one-to-one correspondence to the base tables, with INSTEAD OF triggers on each to perform the required actions.
Thanks for your reply,andrewst.
The reason I could not set up a trigger on table is:
this oracle is handled by other person. They do not wanna open
any autority to outside except the view they created. So all my code
should based on that view, no matter it is trigger, or some thing else,
as long as I can catch if there are any change for the view result.
That means the dba won't update, insert/delete view, they will do those
operation on the basic tables. Just whenever those modification will make the view query result different, I need to catch the difference.
Sounds like I can not achieve it via the trigger on view.
or any other way I can do?
thanks a lot,
yes, I do.
I know if I query the view, I can get all the data. But the problem is
I will get ALL data no matter it changed or not comparing to last time I query the view. That means I have to store my query(on the view) result
somewhere, and every time I do a new query on the view, I will compare it with old data set, find out the difference, then only deal with
those difference. But that will require a lot of work to do.
That's why I wanna find out if there is some way (trigger, or any method) I can use, to find out the difference from view directly, instead of comparison of view's old dataset and new dataset.
I know if table's contents changed, the corresponding view's contents will be changed, and I can get all results via a simple query. However, my focus is to get the change since last time, not the whole result dataset.
Thanks for your help again.
OK, I understand you better now. But I don't think I can help much. You want to know what has changed in the view (or in the tables the view is based on) since last time you looked. Here are the only ways I know:
1) Put created_date and updated_date columns on the base tables, and select them in the view. Then select from the view where the dates are since the date when you last looked. However, this would miss DELETEs.
2) Put triggers on the base tables of the view to write to an audit table - but you have already ruled that out.
3) Save a copy of the data to another table so you can compare later.