Re: Which one is better? Oracel 9i or DB2 7.2??

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 3 May 2002 20:05:12 -0700
Message-ID: <45a06b65.0205031905.38076229@posting.google.com>

"The Nomad" <nobody_at_nowhere.com> wrote in message news:<E%xA8.386545$K52.62427326_at_typhoon.tampabay.rr.com>...
> Oracle 8i
> =======
> In update triggers, you cannot modify the table that triggered the update -
> the result will be something they call a "mutating table" error.
> The only way around this is to create an extremely complex package of
> "trigger helper functions" and copy data into temporary arrays. This is very
> painful.

Wel you can, and you don't need arrays, temporary or otherwise.

It takes less than five minutes and is trivial, and simple rather than painful.

The below examples show how to modify the data in the table being updated, both in the column being updated and in an unrelated column. I really don't see what the problem is unless I misunderstood your requirement.

martin_at_BUB> create table t (text varchar2(10));

Table created.

martin_at_BUB> create or replace trigger modifyme

  2      before update on t
  3      for each row
  4      begin
  5          if :new.text = 'modifyme' then
  6              :new.text := 'modified';
  7          end if;
  8      end;

  9 /

Trigger created.

martin_at_BUB> insert into t values ('fred');

1 row created.

martin_at_BUB> select text from t;

TEXT

---

fred

martin_at_BUB> update t set text = 'modifyme';

1 row updated.

martin_at_BUB> select text from t;

TEXT

---

modified

martin_at_BUB> drop table t;

Table dropped.

martin_at_BUB> create table t (text varchar2(10), othercolumn varchar2(10));

Table created.

martin_at_BUB> create or replace trigger modifyme

  2      before update on t
  3      for each row
  4      begin
  5          if :new.text = 'modifyme' then
  6              :new.text := 'modified';
  7              :new.othercolumn := 'modified';
  8          end if;
  9      end;

 10 /

Trigger created.

martin_at_BUB> insert into t values ('fred','fred');

1 row created.

martin_at_BUB> select text, othercolumn from t;

TEXT OTHERCOLUM
---------- ----------
fred fred

martin_at_BUB> update t set text = 'modifyme';

1 row updated.

martin_at_BUB> select text, othercolumn from t;

TEXT OTHERCOLUM
---------- ----------
modified modified

>
> When returning resultsets from Oracle 8i Stored functions (yes - you read
> that right - Oracle 8i cannot return result sets from stored procedures -you
> must create them as stored functions),
>

This is also false:

martin_at_BUB> create or replace package types as   2 type t_cursor is ref cursor;
  3* end;
martin_at_BUB> /

Package created.

martin_at_BUB> select text, othercolumn from t;

TEXT OTHERCOLUM
---------- ----------
modified modified

martin_at_BUB> insert into t values ('second','row');

1 row created.

martin_at_BUB> commit;

Commit complete.

martin_at_BUB> select text, othercolumn from t;

TEXT OTHERCOLUM
---------- ----------
modified modified
second row

martin_at_BUB> create or replace procedure getrows (rc out types.t_cursor) is
  2 begin
  3 open rc for select text, othercolumn from t;   4 end;
martin_at_BUB> /

Procedure created.

martin_at_BUB> var rc refcursor
martin_at_BUB> exec getrows(:rc)

PL/SQL procedure successfully completed.

martin_at_BUB> print rc

TEXT OTHERCOLUM
---------- ----------
modified modified
second row

martin_at_BUB> Received on Fri May 03 2002 - 22:05:12 CDT