Xref: alice comp.databases.oracle.server:22173
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.bbnplanet.com!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle Triggers
Date: Thu, 04 Jun 1998 19:06:57 GMT
Organization: Oracle Government
Lines: 80
Message-ID: <3576f036.19914275@192.86.155.100>
References: <6l6nji$rgs$1@nnrp1.dejanews.com>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to GlennThames@my-dejanews.com
(if that email address didn't require changing)
On Thu, 04 Jun 1998 18:04:35 GMT, you wrote:

>I am writing an update Trigger and need to insert a row into the same table
>that initiated the trigger.  Can I do this in Oracle?  If I can, How?  Thanks.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/   Now offering spam-free web-based newsreading


The way to do this is to defer inserting into the table until after all of the
row level changes have been made.  Below is an example.  We use a package to
maintain a state across the triggers.  the first trigger, a BEFORE trigger,
simply resets the package state to some know state.  the second trigger collects
all of the rowids affected by the update into a table.  The third trigger
contains all of the logic you want to perform for the affected rows.  You will
loop over the entries in the pl/sql table.  My example computes the average
salary by dept for each updated employee record.... showing you how to read the
table using the saved rowid.


create or replace package emp_pkg
as
    type ridArray is table of rowid index by binary_integer;
    rids    ridArray;
    cnt     number;
end;
/
 
create or replace trigger emp_bu
before update on emp
begin
    emp_pkg.cnt := 0;
end;
/
 
create or replace trigger emp_aufer
after update on emp
for each row
begin
    emp_pkg.cnt := emp_pkg.cnt + 1;
    emp_pkg.rids( emp_pkg.cnt ) := :new.rowid;
end;
/
 
create or replace trigger emp_au
after update on emp
declare
    avg_sal number;
begin
    for i in 1 .. emp_pkg.cnt loop
        select avg(sal) into avg_sal
          from emp
         where deptno = ( select deptno
                            from emp
                           where rowid = emp_pkg.rids(i) );
 
        -- do something about it.....
        dbms_output.put_line( emp_pkg.rids(i) || ' avg = ' || avg_sal );
    end loop;
end;
/
 
update emp set ename = upper(ename);

 
Thomas Kyte
tkyte@us.oracle.com
Oracle Government
Herndon VA
 
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.