| Subject | Re: [IBO] Calculating data |
|---|---|
| Author | |
| Post date | 2014-09-07T11:17:22Z |
Hi Marcin,I am getting error message when I try to compile it. The Staff_code is the primary key in the Staff table and the foreign key in the Roles table.
SET TERM ^ ;
CREATE OR ALTER TRIGGER CALCHOURS FOR ROLES
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 1
AS
DECLARE VARIABLE STAFF_CODE INTEGER;
DECLARE VARIABLE SUM_OF_HOURS INTEGER;
BEGIN
IF (INSERTING OR DELETING OR (UPDATING AND (NEW.HOURS IS DISTINCT FROM OLD.HOURS))
THEN BEGIN
IF (DELETING)THEN STAFF_CODE = OLD.STAFF_CODE;
ELSE STAFF_CODE = NEW.STAFF_CODE;
SELECT SUM(HOURS) FROM ROLES
WHERE STAFF_CODE = :STAFF_CODE
INTO :SUM_OF_HOURS;
UPDATE STAFF
SET HOURS = COALESCE(:SUM_OF_HOURS, 0)
WHERE STAFF_CODE = :STAFF_CODE;
END
END
END^
SET TERM ; ^
The error is;
Message:isc_dsql_prepare_failed
SQL Message : - 104
Invalid token
Engne code : 335544569
Engine Message:
SQL eoor code = -104
Token unknown - line 10, column 5
THEN
Have I coded it incorrectly?
Terry