Re: Oracle Triggers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 04 Jun 1998 19:06:57 GMT
Message-ID: <3576f036.19914275@192.86.155.100>

A copy of this was sent to GlennThames_at_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_at_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. Received on Thu Jun 04 1998 - 14:06:57 CDT