RE: Development question - Trigger to disallow UPDATE without WHERE clause?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Michael Moore' <michaeljmoore@xxxxxxxxx>, "'phil@xxxxxxxxxx'" <phil@xxxxxxxxxx>
- Date: Fri, 27 Jan 2012 14:43:42 -0600
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@xxxxxxxxx]
Sent: Friday, January 27, 2012 2:38 PM
To: phil@xxxxxxxxxx
Cc: Taylor, Chris David; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx> 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@xxxxxxxxxxxxxxx> 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
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
