Re: TRIGGER

On Sep 4, 2:55 pm, zamba <CRISTIAN_FERR..._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 !!!!!!!!

By "id_online_collection that is is referenced to GCCB_COLLECTION_NOTICE_BILL", do you mean the column gccb_cheque.id_online_collection is defined as a foreign key referencing gccb_collection_notice_bill.id_online_collection as its parent? Can gccb_cheque.id_online_collection be null?

btw PL/SQL has a handy ":=" assignment operator that saves a lot of queries of DUAL. Also I don't think the NVL around COUNT(*) does anything. Received on Sat Sep 06 2008 - 05:36:29 CDT