Terry

W dniu 05.09.2014 13:59, tblac@... [IBObjects] pisze:

>
>
> Hi Marcin, I need help coding the trigger.
> I have a relational database with 2 tables. The primary and the
> secondary as shown. If you add up the hours worked in the secondary
> table for Smith it is 24 so hours in the primary table = 24
>
> Primary Table
> id surname hours
> 100 smith 24
>
> Secondary Table
> id Work Hours
> 100 McDonalds 10
> 200 BMW 6
> 100 Google 8
> 100 Microsoft 6
> 300 Oracle 5
>
> Any help would be appreciated.
>
> Terry
>

Having this tables structure it is even simpler than I expected:

CREATE OR ALTER TRIGGER SECONDARY_TABLE_AIUD FOR SECONDARY_TABLE
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 1
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE SUM_OF_HOURS INTEGER;
--(only if column hours is type of integer, if you intend to use
fractions use numeric(9,1))
BEGIN
IF (INSERTING OR DELETING OR (UPDATING AND (NEW.HOURS IS DISTINCT FROM
OLD.HOURS))
THEN BEGIN
IF (DELETING)
THEN ID = OLD.ID;
ELSE ID = NEW.ID;
SELECT SUM(HOURS)
FROM SECONDARY_TABLE
WHERE ID = :ID
INTO :SUM_OF_HOURS;

UPDATE PRIMARY_TABLE
SET HOURS = COALESCE(:SUM_OF_HOURS, 0)
WHERE ID = :ID;
END
END

That's all. I recommend creating the index for ID column in SECONDARY_TABLE.
Having this trigger every time something changes in secondary table,
"hours" column of primary table gets updated.

HTH
Marcin