RE: Development question - Trigger to disallow UPDATE without WHERE clause?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, 'Phil Jones' <phil@xxxxxxxxxx>
- Date: Fri, 27 Jan 2012 14:45:03 -0600
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
Other related posts:
