Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: help with triggers and mutating tables

Re: help with triggers and mutating tables

From: <sybrandb_at_hccnet.nl>
Date: Fri, 18 May 2007 23:32:34 +0200
Message-ID: <496s431ql45sppcde7iamv6o5ontpc50lb@4ax.com>


On 18 May 2007 08:33:25 -0700, heidi <heidistettner_at_gmail.com> wrote:

>I posted a reply yesterday,but it seems to have never made it out.
>Here's a very simple illustration of the problem. First the tables
>and the triggers:
>
>create table foo (
> x number,
> y number,
> c varchar(12),
>)
>/
>create or replace package foo_pkg
>as
> type array is table of foo%rowtype index by binary_integer;
> inserted array;
> deleted array;
> empty array;
> fire boolean default TRUE;
>end;
>/
>create or replace trigger foo_bu
>before update
>on foo
>begin
> foo_pkg.inserted := foo_pkg.empty;
> foo_pkg.deleted := foo_pkg.empty;
>end;
>/
>create or replace trigger foo_au_trigger
> after update
> on foo
>declare
> sum_x number;
> sum_y number;
>begin
> if ( foo_pkg.fire )
> then
> foo_pkg.fire := FALSE;
> for fooPtCnt in 1 .. foo_pkg.inserted.count
> loop
> if ( foo_pkg.deleted( fooPtCnt ).x <>
>foo_pkg.inserted( fooPtCnt ).x )
> then
> select sum(x) into sum_x
> from foo where foo.c != ' ';
> update foo set x=sum_x where foo.c = ' ';
> end if;
> if ( foo_pkg.deleted( fooPtCnt ).y <>
>foo_pkg.inserted( fooPtCnt ).y )
> then
> select sum(y) into sum_y
> from foo where foo.c != ' ';
> update foo set y=sum_y where foo.c = ' ';
> end if;
> end loop;
> foo_pkg.fire := TRUE;
> end if;
>end;
>/
>
>The triggers are set up to ensure that the row with c=' ' always
>contains the sum of x and y from the row where c != ' '. The
>following sql illustrates that is works correctly if I only update x
>or y in a sql statement, but not if I update both at once.
>
>> select * from foo;
>
> X Y C
>---------- ---------- ------------
> 6 8
> 1 2 a
> 4 4 b
> 1 2 c
>
>> update foo set x=3 where c='a';
>
>1 row updated.
>
>> select * from foo;
>
> X Y C
>---------- ---------- ------------
> 8 8
> 3 2 a
> 4 4 b
> 1 2 c
>
>> update foo set y=4 where c='a';
>
>1 row updated.
>
>> select * from foo;
>
> X Y C
>---------- ---------- ------------
> 8 10
> 3 4 a
> 4 4 b
> 1 2 c
>
>> update foo set x=2,y=3 where c='c';
>
>1 row updated.
>
>> select * from foo;
>
> X Y C
>---------- ---------- ------------
> 9 10
> 3 4 a
> 4 4 b
> 2 3 c
>
>
>

Your approach is flawed on many counts.
First of all, why do you store the sum at all? It easy enough of to calculate when required.
You are denormalizing your table by storing data which can be calculated easily.
Secondly, you could do with calculating the sum only once, with an after update statement. The overhead you introduce by trying to be 'smart' is fully obliterating the resources you intend to save. Thirdly, you don't seem to be aware Oracle doesn't distinguish between the empty string and NULL. Your code will fail when you nullify x or y.
Fourthly, using your for each row trigger, you should insert a record only when x or y did change, as during that trigger it is very easy to check whether there was any change.

I would advise you to go back to your drawing board, ask yourself why all your columns are NULL allowed, why your table has no PRIMARY key, and why you are misusing Oracle as a flat file system, instead of a RDBMS.
If you insist on keeping this mess, you should determine the new values of x and y for c='' only once, in an after update trigger.

Hopefully this is a playground instead of an application.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Fri May 18 2007 - 16:32:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US