> Can you send the entire DDL of the triggers in question:
> Pi_Fel_voucher_Status_chg and Pi_Fel_BookingStatusChange.
>
> Perhaps the firing order of the trigger is messed up, e.g. the trigger,
> which is setting the PK value fires after other triggers which also use
> the ChangeRef field.
>

Hi Thomas

Sure heres the DDL, how it helps ...


CREATE TABLE Pi_Fel_StatusChange
(
ChangeRef BigInt NOT NULL,
VoucherNum VarChar(20),
OldStatus Char(1),
NewStatus Char(2),
ChangeDate TimeStamp,
ActualChangeDate TimeStamp,

CONSTRAINT Pi_Fel_StatusChange_ChangeRef PRIMARY KEY(ChangeRef )
)^

CREATE TRIGGER Pi_Fel_StatusChange_Add FOR Pi_Fel_StatusChange
BEFORE INSERT POSITION 0
AS BEGIN
IF (NEW.ChangeRef IS NULL) THEN
NEW.ChangeRef = GEN_ID(Pi_Fel_StatusChange_Gen, 1);

IF (NEW.ChangeDate IS NULL) THEN
NEW.ChangeDate = CURRENT_TIMESTAMP;

IF (NEW.ActualChangeDate IS NULL) THEN
NEW.ActualChangeDate = CURRENT_TIMESTAMP;
END^




CREATE OR ALTER TRIGGER PI_FEL_VOUCHER_STATUS_CHG FOR PI_FEL_VOUCHER ACTIVE
AFTER INSERT OR UPDATE POSITION 1
AS BEGIN

/* deal with new inserts */
IF (OLD.StatusChar IS NULL) THEN BEGIN
/* New issue, use issue date */
INSERT INTO Pi_Fel_StatusChange (VoucherNum, OldStatus, NewStatus,
ChangeDate)
VALUES (NEW.VoucherNum, OLD.StatusChar, NEW.StatusChar, NEW.IssueDate);
END
ELSE BEGIN

IF (OLD.StatusChar <> NEW.StatusChar) THEN BEGIN
IF (NEW.StatusChar = 'E' OR NEW.StatusChar = 'M' ) THEN BEGIN
INSERT INTO Pi_Fel_StatusChange (VoucherNum, OldStatus, NewStatus,
ChangeDate)
VALUES (NEW.VoucherNum, OLD.StatusChar, NEW.StatusChar, NEW.StatusDate);
END
ELSE BEGIN
INSERT INTO Pi_Fel_StatusChange (VoucherNum, OldStatus, NewStatus)
VALUES (NEW.VoucherNum, OLD.StatusChar, NEW.StatusChar);
END
END

END
END^


CREATE TRIGGER pi_fel_bookingStatusChange FOR Pi_Fel_Booking
ACTIVE AFTER UPDATE POSITION 1
AS BEGIN
IF (NEW.StatusChar = 'M' AND NEW.StatusChar <> OLD.StatusChar) THEN BEGIN
/* Update voucher status to match booking status */
UPDATE Pi_fel_voucher v SET v.StatusChar = 'M', StatusDate =
NEW.FlightDate WHERE v.VoucherNum = New.VoucherNum;
END
END^


Note I made a mistake in my original description as
pi_fel_bookingStatusChange actually dosen't even touch the
Pi_Fel_StatusChange so it seems strange it would even be reported in the
error message.

> ISC 335544347
> validation error for column ChangeRef, value "*** null ***"
> At trigger 'Pi_Fel_voucher_Status_chg'
> At trigger 'Pi_Fel_BookingStatusChange"



> --
> With regards,
>
> Thomas Steinmaurer
> Upscene Productions
> http://www.upscene.com
> http://blog.upscene.com/thomas/
>
> Download LogManager Series, FB TraceManager today!
> Continuous Database Monitoring Solutions supporting
> Firebird, InterBase, Advantage Database, MS SQL Server
> and NexusDB!
>
>
> ------------------------------------------------------------------------
>
> No virus found in this message.
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 10.0.1325 / Virus Database: 1500/3611 - Release Date: 05/02/11
>