Re: Development question - Trigger to disallow UPDATE without WHERE clause?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: phil@xxxxxxxxxx
- Date: Fri, 27 Jan 2012 12:37:50 -0800
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:
