RE: Development question - Trigger to disallow UPDATE without WHERE clause?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Michael Moore' <michaeljmoore@xxxxxxxxx>
- Date: Fri, 27 Jan 2012 15:00:21 -0600
Right.
The views are good idea. I'll mention that.
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:59 PM
To: Taylor, Chris David
Cc: Phil Jones; oracle-l@xxxxxxxxxxxxx
Subject: Re: Development question - Trigger to disallow UPDATE without WHERE
clause?
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/0
> 713.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:
