Need Trigger to fail update w/o where clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Need Trigger to fail update w/o where clause

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I need a trigger that will fail updates without where clauses.

    Any Ideas (or samples) would be greatly appreciated.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Probably it's impossible, using standard decisions and only 1 trigger.
    And it's impossible in BEFORE (row or statment) triggers only.

    Not best, but may be only one way:

    1. create package trg_upd
    -- procedure set_new_update;
    -- procedure add_upd_row;
    -- function get_n_rows return number;
    -- r_count number;
    procedure set_new_update is
    begin
    r_count := 0;
    return;
    end;

    procedure add_upd_row is
    begin
    r_count := r_count+1;
    return;
    end;

    function get_n_rows return number is
    begin
    return r_count;
    end;
    ...

    2. trigger BEFORE (statment)
    ...
    trg_upd.set_new_update;

    3. trigger BEFORE (row)
    ...
    trg_upd.add_upd_row;

    4.trigger AFTER (statment)
    ...
    n number;
    begin
    select count(*) into n from TTT;

    if n = trg_upd.get_n_rows then
    raise_application_error(-20100, 'Updated all rows in TTT, discard statment');
    ...

    May be it help. (but I don't sure)





  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Excellent Ideas. Here's what we'll do if the app will support it. Thanks!

    _DML.UpdateRow(pk_id,col_to_update,new_val)
    _DML.DeteteRow(pk_id)

    This will save us from restores when someone forgets the where clause (and remembers to commit)!

    -Ken

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Next way: common idea.

    IN BEFORE STATMENT trigger

    1) get # of session from user context

    select distinct sid from v$mystat;

    2) get current UPDATE statment from sql area (using # of session)

    3) check statment

    if instr( upper(stmt), 'WHERE') = 0 then
    raise_application_error(....);
    else null;
    end if;

    ..

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I'm having a prolem running that trigger. The problem is that the moment you execute the select statment, it becomes the "current" statement for the "current" session.

    Is there any way to do this with auditing? Or a database trigger?

    One ugly solution is to have all updates and deletes done by proxy through an audit table. If you wanted to delete a row, for example, you would insert the primary key and a 'D' in a table. A trigger on that table would perform the operation only if the primary key field was inserted... The effect is to restrict a delete to one valid primary key. That, in effect, is like restricting the original delete's where clause.

    Only problem is that the developer would have to re-write a ton of code.

    -Ken

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    OK. O got it.
    It happen when we used v$session, but if we will use v$open_cursor, then
    probably (but i don't sure) we can get information about all open cursors.

    Problem is:
    When oracle fire BEFORE STATMENT trigger it has open cursor for this statment or not?

    I want check statment BEFORE first row will be update.
    In first my message we can check it AFTER all rows updated, and if we have
    table with 1000000 records than we must use huge rollback segment and will lost a lot of time for this kind of upbate.
    It was my reasons, when i wrote second way to solve problem.

Posting Permissions

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



Click Here to Expand Forum to Full Width