Moving Logic Into the Database - Page 3

Now for the trigger which will activate this function:

CREATE TRIGGER projtask_insert_depend_trig 
        BEFORE INSERT OR UPDATE ON project_task
        FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();

Now for the more confusing process. When a task is updated, we need to look at the tasks that are dependent on us. If Task B has a start date that is the same as our end date, that means Task B was constrained by us, and we need to push him back if we are delayed, or bring him forward if we move our end date up.

But what if Task B is also dependent on another task, say Task D? If we try to bring Task B forward too much, we might be in violation of that constraint -- we might be trying to start Task B before Task D is completed.

No problem! Our "before" trigger protects us, no matter what. If we try to move Task B forward too much, Task B's triggers are going to fire and push him back again! We're getting all of this for free because we have put a few lines of code inside of the database itself. Every row becomes its own boss. Trying to perform this maneuver inside of PHP would be mind-bending to say the least.

CREATE OR REPLACE FUNCTION projtask_update_depend () RETURNS OPAQUE AS '
DECLARE
    dependent RECORD;
    dependon RECORD;
    delta   INTEGER;
BEGIN
    --
    --  See if tasks that are dependent on us are OK
    --  See if the end date has changed
    --
    IF NEW.end_date > OLD.end_date THEN
        --
        --  If the end date pushed back, push back dependent tasks
        --
        FOR dependent IN SELECT * FROM project_depend_vw
            WHERE is_dependent_on_task_id=NEW.project_task_id LOOP
            --
            --  Some dependent tasks may not start immediately
            --
            IF dependent.start_date > OLD.end_date THEN
                IF dependent.start_date < NEW.end_date THEN
                    delta := NEW.end_date-dependent.start_date;
                    UPDATE project_task
                        SET start_date=start_date+delta,
                        end_date=end_date+delta
                        WHERE project_task_id=dependent.project_task_id;
                END IF;
            ELSE
                IF dependent.start_date = OLD.end_date THEN
                    delta := NEW.end_date-OLD.end_date;
                    UPDATE project_task
                        SET start_date=start_date+delta,
                        end_date=end_date+delta
                        WHERE project_task_id=dependent.project_task_id;
                END IF;
            END IF;
        END LOOP;
    ELSIF NEW.end_date < OLD.end_date THEN
            --
            --  If the end date moved up, move up dependent tasks
            --
            FOR dependent IN SELECT * FROM project_depend_vw 
                WHERE is_dependent_on_task_id=NEW.project_task_id LOOP
                IF dependent.start_date = OLD.end_date THEN
                    --
                    --  dependent task was constrained by us - bring it forward
                    --
                    delta := OLD.end_date-NEW.end_date;
                    UPDATE project_task
                        SET start_date=start_date-delta,
                        end_date=end_date-delta
                        WHERE project_task_id=dependent.project_task_id;
                END IF;
            END LOOP;
    END IF;
--
--  MAY WISH TO INSERT AUDIT TRAIL HERE FOR CHANGED begin/end DATES
--
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

Now for the trigger that will activate the function above. This trigger only needs to fire on "updates" to the database, since no other tasks could be dependent on a brand new task when it is first inserted.

CREATE TRIGGER projtask_update_depend_trig 
    AFTER UPDATE ON project_task 
    FOR EACH ROW EXECUTE PROCEDURE projtask_update_depend();