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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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