Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Jan 13, 2003

Moving Logic Into the Database - Page 3

By DatabaseJournal.com Staff

by Tim Perdue



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();


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM