Re: TRIGGER

On Thu, 4 Sep 2008 06:55:14 -0700 (PDT), zamba <CRISTIAN_FERRERO_at_hotmail.com> wrote:

>HI EVERYBODY i have this trigger :
>
>
>CREATE OR REPLACE TRIGGER FORMASGC.TR_CAMB_EST_CHEQUE
> after insert or update of cod_status_cheque or delete ON
>FORMASGC.GCCB_CHEQUE for each row
>declare
>
> ls_new varchar2(10);
> ls_old varchar2(10);
>
>
>begin
> if inserting or updating then
>
> select
> :new.update_user,
> :new.update_program,
> :new.id_online_collection,
> :new.id_branch,
> :new.cheque_number,
> :new.cheque_amount,
> :new.cod_status_cheque,
> nvl(:old.cod_status_cheque,'ESTCHE0000'),
> :new.create_date,
>-- :new.f_acreditacion,
> :new.update_date ,
> :new.validate_date,
> :new.id_online_collection
> into
> lrt_cheques.update_user,
> lrt_cheques.update_program,
> lrt_cheques.id_online_collection,
> lrt_cheques.id_branch,
> lrt_cheques.cheque_number,
> lrt_cheques.cheque_amount,
> ls_new,
> ls_old,
> lrt_cheques.create_date,
>-- lrt_cheques.f_acreditacion,
> lrt_cheques.update_date,
> lrt_cheques.validate_date,
> lrt_cheques.id_online_collection
> from dual;
>
>
> end if;
>
> select
> NVL(COUNT(*),0)
> into ll_cant_cierre
> from GCCB_COLLECTION_NOTICE_BILL
> where id_online_collection=
>lrt_cheques.id_online_collection;
>
> insert into salida_trigger values (ll_cant_cierre);
>
>end
>
>how is posible that ll_cant_cierre is 0 if i'm doing an after trigger
>and it suppouse that first i do the insert in the table, and i'm
>storing id_online_collection that is is referenced to
>GCCB_COLLECTION_NOTICE_BILL and then fires the trigger...why trigger
>can't find values in GCCB_COLLECTION_NOTICE_BILL with the value that
>was stored in GCCB_CHEQUE (wich is referenced to
>GCCB_COLLECTION_NOTICE_BILL)
>
>
>thanks !!!!!!!!

The target of a select into is a pl/sql variable. The pl/sql variables you select into have not been defined in the trigger code. Nor is there any exception trapping. The pl/sql variables might have been defined as global variables in a package, but as you don't post any background, and don't post a version, and consequently don't provide enough information no one can resolve your 'mystery'.

-- 
Sybrand Bakker
Senior Oracle DBA