At 11:01 PM 11/08/2007, you wrote:

>hello one and all,
>
>ok, so i added some code to the troublsome triggers to log entries in
>a separate generic VARS table that i created to track the firing of
>the suspect triggers. it took a bit of time to collect the data.
>here is the code of one of the suspect triggers:
>
>CREATE TRIGGER ROMECH_AI FOR ROMECH AFTER INSERT
>AS
> DECLARE VARIABLE adate CHAR(1);
> DECLARE VARIABLE idate DATE;
> DECLARE VARIABLE fdate DATE;
> DECLARE VARIABLE ctotal NUMERIC(15,3);
> DECLARE VARIABLE rtotal NUMERIC(15,3);
> DECLARE VARIABLE id DATE;
> DECLARE VARIABLE fd DATE;
>BEGIN
>INSERT INTO VARS (EVENT,SI1,SI2,SI3,SI4,RF1,RF2) VALUES ("BEGIN
>ROMECH_AI",NEW.IDIV,NEW.IRO,NEW.IROVMRS,NEW.IMECHANIC,NEW.CTOTAL,NEW.RTOTAL);
>SELECT R.IDATE, R.FDATE, R.CTOTAL_MECH, R.RTOTAL_MECH FROM RO R WHERE
>(R.IDIV = NEW.IDIV) AND (R.IC_RO = NEW.IRO) INTO :idate, :fdate,
>:ctotal, :rtotal;
>id = :idate;
>fd = :fdate;
>IF (:ctotal IS NULL) THEN
> ctotal = 0.00;
>IF (:rtotal IS NULL) THEN
> rtotal = 0.00;
>SELECT D.IRO_AUTO_DATE FROM DIVISION D WHERE (D.IC_DIVISION =
>NEW.IDIV) INTO :adate;
>IF (:adate = "T") THEN BEGIN
> IF (NEW.IDATE < :idate) THEN
> id = NEW.IDATE;
> IF (NEW.IDATE > :fdate) THEN
> fd = NEW.IDATE;
> UPDATE RO R SET R.IDATE = :id, R.FDATE = :fd, R.CTOTAL_MECH = :ctotal
>+ NEW.CTOTAL, R.RTOTAL_MECH = :rtotal + NEW.RTOTAL WHERE (R.IDIV =
>NEW.IDIV) AND (R.IC_RO = NEW.IRO);
> END
>ELSE
> UPDATE RO R SET R.CTOTAL_MECH = :ctotal + NEW.CTOTAL, R.RTOTAL_MECH =
>:rtotal + NEW.RTOTAL WHERE (R.IDIV = NEW.IDIV) AND (R.IC_RO = NEW.IRO);
>INSERT INTO VARS (EVENT,SI1,SI2,SI3,SI4,RF1,RF2,RF3,RF4) VALUES ("END
>ROMECH_AI",NEW.IDIV,NEW.IRO,NEW.IROVMRS,NEW.IMECHANIC,NEW.CTOTAL,NEW.RTOTAL,:ctotal,:rtotal);
>END~
>
>as you can see, i insert into VARS at the beginning and end of the
>trigger. this is working flawlessly. the only thing that i can
>ascertain at this point is that sometimes the "UPDATE RO R..." is just
>not adding the, for instance, ":ctotal + NEW.CTOTAL", or the update
>line is just not running. because, simply, it is just not adding.
>the values are there to be added and they are NOT NULL but both are a
>valid zero or number. the WHERE statement of the "UPDATE RO R..." is
>also fine and valid because it is working at least 75% of the time
>with other detail records for the same master record. the RO table is
>the master and the ROMECH is the detail.
>
>so any suggestions why the "UPDATE RO R..." line does not seem to be
>properly adding the two values or is not updating the master RO table
>properly some 5 to 25% of the time? especially when the same code is
>properly executing most of the time? perhaps my approach or syntax is
>off. always willing to learn new stuff. thanx for the help and talk
>to you soon.

Unpredictable logic seems to be the source of your problems
here. You are assuming that the variables idate and fdate will be
non-null. However, if the SELECT statement does not return any
result (because there is no match), these variables will be null and
your "IF" tests will not return the results you expect. You might
like to visit the Documentation Index at the main Firebird website
and download the Null Guide to understand why. Or, if you already
understand the logic of nulls, add some code to cover the null cases.

I suggest also that you use the proper syntax for quoting
strings: single-quotes. Double quotes have a different syntactic
function in SQL.

./heLen