Re: Development question - Trigger to disallow UPDATE without WHERE clause?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Fri, 27 Jan 2012 12:59:09 -0800
Hi guys,
my example of WHERE 1 = 1 was meant to point to the idea that a WHERE
clause does not guarantee a sufficient degree of selectivity. The were
clause might be WHERE STATUS='Y'. IF 97% of the records have
status='Y', is that selective enough to meet the needs of your
application?
You might look into updatable views. UNGRANT UPDATE on the main table
and grant UPDATE on each of the UPDATABLE views. Those views would
have hard coded WHERE clauses.
Mike
On Fri, Jan 27, 2012 at 12:45 PM, Taylor, Chris David
<ChrisDavid.Taylor@xxxxxxxxxxxxxxx> wrote:
> Doh! (mumble something about chickens...hatching...counting...)
>
> Symptoms
> Trigger with ORA_SQL_TXT Returns NULL After Upgrade [ID 438324.1]
>
> On database versions 9.2.0.1 to 9.2.0.6 ora_sql_text works and returns the
> calling text for dml triggers, where as starting from 9.2.0.7 the behavior
> has changed and returns NULL.
> Cause
> This issue had surfaced from 9.2.0.7.0 patchset. After discussions in Bug
> 4171597 which was closed as a duplicate of Bug 4230721 it was concluded that
> it was the expected behavior i.e ORA_SQL_TXT should return null when dml
> triggers are used.
>
> ORA_SQL_TXT is a "System defined event attribute" and is supposed to work
> only with "System triggers". This is also what the Documentation says -
> "Application Developers Guide : Fundamentals(9.2)
> Chapter 16 Working with System events".
>
> Solution
> Expected behavior.
>
>
> On versions 9.2.0.1 to 9.2.0.6 was the erroneous behavior. The correct
> behavior is what is found starting with version 9.2.0.7 and up.
>
>
> --------------------------------------------------------------------------------
>
>
>
> 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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Taylor, Chris David
> Sent: Friday, January 27, 2012 2:36 PM
> To: 'Phil Jones'
> Cc: 'oracle-l@xxxxxxxxxxxxx'
> Subject: RE: Development question - Trigger to disallow UPDATE without WHERE
> clause?
>
> I believe this will work
>
> http://www.orafaq.com/usenet/comp.databases.oracle.server/2005/01/13/0713.htm
>
> Glad those comp.databases.oracle.server archives are found in various places.
>
> +1 Tom Kyte (heh)
>
> 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)
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
