SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from RDB$TRIGGERS where rdb$TRIGGER_NAME = 'AGGIORNA_ALLESTIMENTO')) then 
    BEGIN
    execute statement 'drop trigger AGGIORNA_ALLESTIMENTO;';
    END
END^
SET TERM ; ^

COMMIT;
SET TERM ^ ;


CREATE TRIGGER AGGIORNA_ALLESTIMENTO FOR WGHAZIENDA ACTIVE
AFTER UPDATE POSITION 5
AS 
declare VARIABLE old_room VARCHAR(6);
DECLARE VARIABLE new_room VARCHAR(6);
DECLARE VARIABLE allestimento VARCHAR(20);
DECLARE VARIABLE info_agg varchar(30);
DECLARE VARIABLE len_allest INTEGER;
DECLARE VARIABLE len_info INTEGER;
BEGIN 
	old_room='';
	allestimento='';
	if(new.DATA_LAVORO>old.DATA_LAVORO) THEN
	BEGIN
        for select  WGHPRENCAM.NUMCAM,wghvarie.DESCRIZIONE from 
            WGHVARIE inner JOIN WGHVARIEPREN on WGHVARIE.ID_VARIE=WGHVARIEPREN.ID_VARIE, WGHPREN,WGHPRENCAM where
            WGHVARIEPREN.ID_PREN=WGHPREN.CODICE  and WGHPRENCAM.CODPREN=wghpren.CODICE
            and WGHVARIE.DESCRIZIONE like '%+'   and wghpren.ARRIVO=(
                select min(s.ARRIVO) from WGHVARIE p inner JOIN WGHVARIEPREN q on p.ID_VARIE=q.ID_VARIE, WGHPRENCAM r, wghpren s
                where q.ID_PREN=s.CODICE  and r.CODPREN=s.CODICE
            and p.DESCRIZIONE like '%+' and r.numcam=wghprencam.NUMCAM and r.numcam not in (select wghconto.RECCAM from wghconto where wghconto.flag=5 and wghconto.PARTENZA>new.DATA_LAVORO )) and WGHPRENCAM.NUMCAM is not NULL
            order by 1 INTO :new_room,:info_agg
            do
            BEGIN
            if(old_room='') then old_room=new_room;
            if(new_room<>old_room) then /*registra allestimento*/
                BEGIN
                update WGHAPP set descrizione=:allestimento where codice=:old_room;
                old_room=new_room;
                allestimento='';
                END
            len_allest=STRLEN(allestimento);
            len_info=STRLEN(info_agg);
            if(len_info+len_allest<20) THEN allestimento=allestimento||SUBSTRING(info_agg from 1 for (STRLEN(info_agg)-1))||',';
            
            END
        if(STRLEN(allestimento)>0) then update WGHAPP set descrizione=:allestimento where codice=:new_room; 
    END 
END^
SET TERM ; ^
