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.

lucas