RE: Development question - Trigger to disallow UPDATE without WHERE clause?
Date: Fri, 27 Jan 2012 14:43:42 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6ADD075556_at_SPOBMEXC14.adprod.directory>
You can raise an error if there is no WHERE - of course in that case of 1=1 we'd still have a bug but I don't think any developer is going to write that (unless they're being malicious of course)
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.
-----Original Message-----
From: Michael Moore [mailto:michaeljmoore_at_gmail.com]
Sent: Friday, January 27, 2012 2:38 PM
To: phil_at_phillip.im
Cc: Taylor, Chris David; oracle-l_at_freelists.org
Subject: Re: Development question - Trigger to disallow UPDATE without WHERE clause?
Finding a where clause isn't necessarily going to help you. UPDATE MYTABLE SET mycol = 'hi' where 1 = 1; It has a where clause, but how does that help?
Mike
On Fri, Jan 27, 2012 at 12:28 PM, Phil Jones <phil_at_phillip.im> wrote:
> If v$sql gets populated before any records are actually processed (I imagine it is, but a quick test will verify) & the sql_id in v$session also gets populated after the query has been parsed but before any rows are processed you might be able to do it in a before update trigger that throws an exception. You'll need sys_context('USERENV','SID') to join with the v$ tables.
>
> To be honest, you'd be better off using a cluebat on the devs :)
>
> Phil
>
> On 27 Jan 2012, at 20:13, "Taylor, Chris David" <ChrisDavid.Taylor_at_ingrambarge.com> wrote:
>
>> Our developers are working on a large project that is going to require a lot of scripts to convert data and due to an error in one of the scripts one of our tables was fubar.
>> So the question came up - is there a way in a trigger or other method to trap UPDATE statements that do not have a WHERE clause?
>>
>> I'm searching for this now but wanted to poll the list at the same time. Off the top of my head I couldn't come up with anything.
>>
>> Thanks!
>>
>> Chris
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 27 2012 - 14:43:42 CST