Re: TRIGGER may not see it

Glad you touch the design issue. But I was also reflecting that the problem with FOR EACH ROW triggers is that they basically break the ACID property - or perhaps, more simply said, you are looking at rows when INSERT, DELETE and UPDATE are designed to operate on sets and when the SQL work unit is actually a set. A statement-level trigger is consistent with SQL, a row-level statement isn't (which isn't to say that they are never useful), because when you inspect the object the conclusion you draw may potentially be invalidated by row operations that haven't been performed yet but are part of the presumably unitary SQL operation.

Kind of thing that makes Chris Date go ballistic.

That said, I just love the name "mutating table". Looks so much like a pulp magazine from the 1950s or an Ed Wood movie.

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 02/11/2011 07:16 PM, Jared Still wrote:
> On Fri, Feb 11, 2011 at 5:48 AM, Stephane Faroult
> <sfaroult_at_roughsea.com <mailto:sfaroult_at_roughsea.com>> wrote:
>
> You may find this useful:
>
> http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936
>
>
> I've used that method in a paper/presentation on normalization.
>
> Why do you need the workaround in Kyte's article?
>
> Because you are working with poorly designed database,
> as I am sure you already know. :)
>
> This was the point in my presentation - if you are trying to work around
> mutating table errors, you should revisit the design if possible.
>
> While the workaround for mutating tables does work, it is
> very convoluted. In your case Stephane, I know I am preaching
> to the choir, but there are lots of other readers here. :)
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>

--
http://www.freelists.org/webpage/oracle-l