Subject | Re: [IBO] Calculating data |
---|---|
Author | Svein Erling Tysvær |
Post date | 2014-09-06T10:45:31Z |
>CREATE OR ALTER TRIGGER SECONDARY_TABLE_AIUD FOR SECONDARY_TABLEOne little word of caution: updating the primary table like this, means that no other transaction can INSERT/UPDATE/DELETE the same ID in SECONDARY_TABLE until all other transactions that has done similar transactions to the same ID commits or rolls back (lock conflicts may occur). If there are millions of different IDs, then this will probably not cause problems, if there's only a handful, you may have to look at a more "concurrency friendly" approach (INSERT rather than UPDATE - a more elegant solution, but adds some complexity so you may not want to implement it if Marcins solution is sufficient).
>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
Set