Need Trigger to fail update w/o where clause

I need a trigger that will fail updates without where clauses.

Any Ideas (or samples) would be greatly appreciated.

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)

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

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;

..

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

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.