Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Question Unanswered: help about trigger on view

    HI,
    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
    for :
    create view table_views ......(from table1, table2, table3,...etc)
    create trigger view_trigger instead of update on table_views ......

    update 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: help about trigger on view

    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.

  3. #3
    Join Date
    Mar 2003
    Posts
    4
    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,

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You do realise that a view is nothing more than a stored QUERY? It is not stored DATA? Any changes to the base tables WILL be reflected in the view, without any triggers being required:

    CREATE TABLE t ( col NUMBER );

    CREATE VIEW v AS SELECT col FROM t;

    INSERT INTO t (col) VALUES (123);

    SELECT * FROM v;

    col
    ---
    123

  5. #5
    Join Date
    Mar 2003
    Posts
    4
    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.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Mar 2003
    Posts
    4
    thanks a lot.
    I guess I have no choice except go third way for this case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •