Re: Triggers - Value Passing

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 22 Mar 2005 15:02:38 -0800
Message-ID: <bf463805032215025bd7743e@mail.gmail.com>

On Tue, 22 Mar 2005 02:23:52 -0800 (PST), Kean Jacinta <jacintakean_at_yahoo.com> 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

Received on Tue Mar 22 2005 - 18:06:17 CST