Re: Q: INSERT, UPDATE and how to catch rowid

On 30 Sep 1997 09:15:36 GMT, "Gerard H. Pille" <ghp_at_santens.be> wrote:

>But how would this trigger then pass the new rowid to the program that made
>the insert?

-- 

We can get the rowid of the last row we inserted (updated) into the table
quite easily...

try the following script, the concept is, we will use a package to maintain a
state; a trigger to populate the state.  An after insert for each row trigger
can push the rowid of the newly inserted row into a package variable for us.  We
can read the package variable and use it:

create table foo ( x int );
 
create or replace package foo_pkg
as
    last_row    rowid;
end;
/
 
create or replace trigger foo_aifer
after insert on foo
for each row
begin
    foo_pkg.last_row := :new.rowid;
end;
/
 
insert into foo values (1);
 
set serveroutput on
exec dbms_output.put_line( 'You added ' || foo_pkg.last_row );
select rowid, x from foo;
------------------------------------------------

Table created.
 
 
Package created.
 
 
Trigger created.
 
 
1 row created.
 
You added 00000F35.0000.0004
 
PL/SQL procedure successfully completed.
 
 
ROWID                       X
------------------ ----------
00000F35.0000.0004          1
 
---------------------------------------------------

hope this helps..



>------------

>Kind reGards

>     \ /   |

>      X    |

>     / \   x

>     Gerard

>

>Borivoj Tydlitat <btydlitat_at_merlin.cz> schreef in artikel

><3429116D.65F7_at_merlin.cz>...

>> Starous wrote:

>> > 

>> >   I'm inserting a row into table using command

>> > 

>> >   INSERT INTO my_table

>> >     ( column list )

>> >   VALUES

>> >     ( values list );

>> > 

>> >     After little while I will need to update this new inserted record.

>> >   Using index is good, but I'm looking for better performance.

>> > 

>> >   Is there any chance to catch a rowid of the new inserted record for

>> >   later use? ( The same problem coming with UPDATE too )

>> 

>> Yeah, you can get the ROWIDs in triggers FOR EACH ROW:

>> INSERT:   :new.ROWID                in AFTER INSERT trigger

>> UPDATE:   :new.ROWID and :old.ROWID in AFTER UPDATE trigger

>> DELETE:   :old.ROWID                in BEFORE DELETE trigger

>> 

>> Borek

>> mailto:btydlitat_at_merlin.cz

>> 



Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation