Re: Triggers - Value Passing

On Tue, 22 Mar 2005 02:23:52 -0800 (PST), Kean Jacinta
<jacintakean@xxxxxxxxx> wrote:
...
> My question , If this statement put inside a trigger .
> Then how would a trigger pass the returning value to
> my application ?

Here is one method to do it.

This is not the most efficient method, as each row is 
doing a select from dual, but it may give you some idea
of how to do this.



drop table trg_test_tbl;
drop sequence trg_test_seq;

create sequence trg_test_seq
start with 1
/

create table trg_test_tbl (
        pk number(12) not null,
        first_name varchar2(30) not null,
        last_name varchar2(30) not null
)
/

create or replace package trg_test_pkg
is
        current_pk number(12);
end;
/

create or replace trigger trg_test_trg
before insert on trg_test_tbl
for each row
begin
   select trg_test_seq.nextval into trg_test_pkg.current_pk
        from dual;
        :new.pk := trg_test_pkg.current_pk;
end;
/

== Test ====

begin
        for orec in (
                select substr(object_name,1,30) first_name,
                owner last_name
                from all_objects
                where rownum <= 20
        )
        loop
                insert into trg_test_tbl(last_name, first_name)
                values(orec.last_name, orec.first_name);

                dbms_output.put_line(trg_test_pkg.current_pk);

        end loop;


end;
/


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l

Other related posts: