TRIGGER

[zamba]

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 !!!!!!!!

[sybr...@hccnet.nl]

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

[William Robertson]

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.