the enclosed trigger does not work as expected.

At times, it causes a "multiple rows from singleton select" error.

I investigated the problem and found the culprit in the "select"
starting at /* 1 */.

When I try to insert a tuple (bl_lt.y_mov, bl_lt.n_mov) and the tuple
(bl_hd.y_mov-1, bl_hd.n_mov) also exists, the error is given.

Eg. I want to insert (2003,46) in BL_LT and the tuple (2002,46)
exists in BL_HD, I receive an error.

I could find no logical error in the code.

Can you see something I overlooked ?

TIA

Duilio Foschi






create trigger bl_lt_on_mv_li_aftins for bl_lt
after insert
position 10
as
declare variable n_doc integer;
declare variable d_doc date;
declare variable serie varchar(10);
declare variable d_mag date;
declare variable cod_age varchar(5);
declare variable cod_dst varchar(9);
declare variable c_s varchar(1);
declare variable sign smallint;

begin

if (not exists (select * from mv_hd where mv_hd.y_mov=bl_lt.y_mov
and
mv_hd.n_mov=bl_lt.n_mov) ) then
begin
select /* 1 */
n_doc,
d_doc,
serie,
d_mag,
cod_age,
cod_dst
from bl_hd
where bl_hd.y_mov=bl_lt.y_mov and bl_hd.n_mov=bl_lt.n_mov
into
:n_doc,
:d_doc,
:serie,
:d_mag,
:cod_age,
:cod_dst;

insert into mv_hd (
N_MOV,
Y_MOV,
D_MOV,
N_DOC,
D_DOC,
SERIE,
COD_MAG,
D_MAG,
COD_CAU,
COD_CF,
COD_DST,
COD_AGE
)
values (

bl_lt.N_MOV,
bl_lt.Y_MOV,
bl_lt.D_MOV,
:N_DOC,
:D_DOC,
:SERIE,
bl_lt.COD_MAG,
:D_MAG,
bl_lt.COD_CAU,
bl_lt.COD_CF,
:COD_DST,
:COD_AGE

);


select sum(q_c_acq+q_c_div-q_s_ven-q_s_div) from tb_cau1 where
cod_cau=bl_lt.cod_cau into :sign;
if (sign>0) then
c_s='C';
else
c_s='S';


insert into mv_dg (
N_MOV ,
Y_MOV ,
D_MOV ,
C_S ,
COD_MAG ,
F_REG_ALC ,
F_REG_TAB ,
F_DAA_ESP ,
F_DAA_RIE ,
F_DAA_SCA ,
D_RIE ,
D_PAR ,
D_REG_ALC ,
D_REG_TAB ,
D_DAA_ESP ,
D_DAA_RIE ,
D_DAA_SCA ,
D_DAA_E ,
NOTE
)
values (
bl_lt.N_MOV ,
bl_lt.Y_MOV ,
bl_lt.D_MOV ,
:c_s ,
bl_lt.COD_MAG ,
'S' ,
'S' ,
'S' ,
'S' ,
'S' ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
''
);
end /* if not exists... */

insert into mv_li (
N_MOV,
Y_MOV,
D_MOV,
SINGOLO,
N_ROW,
COD_MAG,
COD_ART,
PARTITA,
LOTTO,
D_SCA,
QTA,
UM,
US,
PREZZO,
N_COLLI,
P_NETTO,
P_LORDO,
COD_CAU,
COD_CF,
SINGOLO
)
values
(
bl_lt.N_MOV,
bl_lt.Y_MOV,
bl_lt.D_MOV,
bl_lt.SINGOLO,
bl_lt.N_ROW,
bl_lt.COD_MAG,
bl_lt.COD_ART,
bl_lt.PARTITA,
bl_lt.LOTTO,
bl_lt.D_SCA,
bl_lt.QTA,
(select um from art where art.cod_art=bl_lt.cod_art),
bl_lt.US,
bl_lt.PREZZO,
bl_lt.N_COLLI,
bl_lt.P_NETTO,
bl_lt.P_LORDO,
bl_lt.COD_CAU,
bl_lt.COD_CF,
bl_lt.SINGOLO
);
end !!