RE: ROWID in statement trigger

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 23 Oct 2002 14:38:49 -0800
Message-ID: <F001.004F1D91.20021023143849@fatcity.com>

> -----Original Message-----
> From: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
>
> have a package type as a plsql table of rowid
>
> statement-before:
> plsql_table.delete;
>
> row-after:
> plsql_table(plsql_table.count+1) := :new.rowid;
>
> statement-after:
> for i in 1 .. plsql_table.count loop
> <play with rowids>
> end loop;

I decided to do a small "proof of concept" of this. Is there another way besides using a "global" package variable? My code follows below.

create package global_vars
as

   type row_tabtype is table of rowid not null index by binary_integer ;    update_rows row_tabtype ;
end ;
/

create table emp
  (id number, name varchar2 (40)) ;
create table emp_upd_history
  (updated_rowid rowid, updated_date date) ;

create trigger emp_b4u1
before update
on emp
begin

    global_vars.update_rows.delete ;
end ;
/

create trigger emp_b4u2
before update
on emp
for each row
begin

    global_vars.update_rows (global_vars.update_rows.count + 1) := :new.rowid ;
end ;
/

create trigger emp_afu1
after update
on emp
begin

    for i in 1..global_vars.update_rows.count     loop

       insert into emp_upd_history
          (updated_rowid, updated_date)
        values
          (global_vars.update_rows (i), sysdate) ;

    end loop ;
end ;
/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Oct 23 2002 - 17:38:49 CDT