Re: Preventing DELETE and UPDATE without a WHERE clause?

> On 6/16/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
>> > Chris Campbell <chris@bignerdranch.com> writes:
>> >> I heard an interesting feature request today: preventing the
>> >> execution of a DELETE or UPDATE query that does not have a WHERE
>> clause.
>> >
>> > These syntaxes are required by the SQL spec.  Furthermore, it's easy
>> > to imagine far-more-probable cases in which the system wouldn't detect
>> > that you'd made a mistake, eg
>> >
>> >       DELETE FROM tab WHERE key > 1
>> >
>> > where you meant to type
>> >
>> >       DELETE FROM tab WHERE key > 10000000
>> >
>> > I suggest counseling your client to learn how to use BEGIN/ROLLBACK.
>> > This proposal strikes me as falling squarely within the rule about
>> > "design a system that even a fool can use, and only a fool will want
>> > to use it".
>> >
>> Just a theory, couldn't a trigger be set up that would case the query to
>> tank if it touches too many rows?
>>
>
> i haven't tried but maybe a FOR STATEMENT trigger AFTER the event can
> ask ROW_COUNT using GET DIAGNOSTICS?

Well, if you *can't" do it in a trigger, maybe that's a valid modification
for Hackers to consider.