Again, this question should be directed to the appropriate IBExpert support forum/list/group.

Sean

HI!

I have had feedback from IBExpert in respect of my block statement. The correct syntax is easy and looks like the following below.

I can see in my trigger log that the update uses just one transaction but unfortunately the triggers fire although the preceding rdb$set for each record (as they normally should).

When I put everything into a block statement (as initially put in this topic) the update has no effect, because the update runs isolated inside of the block.

Maybe someone has a clue?
Thank you
Christian


--This time the Analysis DB is the source
   FBSRC  = ibec_CreateConnection(__ctFirebird,'DBName="PATH to DB1";
   ClientLib=C:\WINDOWS\system32\fbclient.dll;
   user=XX; password=XX; names=ISO8859_1; sqldialect=3');

-- and writes to the Test DB/Prod. DB
   FBDEST  = ibec_CreateConnection(__ctFirebird,'DBName="PATH to DB2";
   ClientLib=C:\WINDOWS\system32\fbclient.dll;
   user=XX; password=XX; names=ISO8859_1; sqldialect=3');

   ibec_UseConnection(FBSRC);
   ibec_UseConnection(FBDEST);

use FBSRC;
for select
        (lrc.paid_claims_eur + lrc.paid_costs_eur - lrc.paid_recoveries_eur + --lrc.paid_fees_eur +
        lrc.os_claims_eur + lrc.os_costs_eur - lrc.os_recoveries_eur) as TCACCY,
        (lrc.paid_claims_usd + lrc.paid_costs_usd - lrc.paid_recoveries_usd + --lrc.paid_fees_usd +
        lrc.os_claims_usd + lrc.os_costs_usd - lrc.os_recoveries_usd) as TCACCY2,
        current_date, lrc.file_id
        from loss_record_claims lrc
        where lrc.file_id in (120966,120214)
into
        :TCACCY, :TCACCY2, :DATEFILTER, :FILE_ID
do
  begin

use FBDEST;
        try
        execute statement 'select rdb$set_context(''USER_TRANSACTION'', ''bulkload'', ''1'') from rdb$database';
        update files f
            set f.ccy_total_claim_amount_net = :TCACCY,
                f.ccy2_total_claim_amount_net = :TCACCY2,
                f.date_filter = :DATEFILTER
        where f.file_id = :FILE_ID;
        except ibec_ShowMessage(ibec_err_Message());
        end
    end
commit;
   ibec_CloseConnection(FBSRC);
   ibec_CloseConnection(FBDEST);
end