• Welcome, Guest. Please login.
 
May 26, 2019, 12:36:37 pm

News:

Welcome to the SQLitening support forums!


Fun with TRIGGERS

Started by Bern Ertl, April 20, 2010, 12:13:35 pm

Previous topic - Next topic

Bern Ertl

April 20, 2010, 12:13:35 pm Last Edit: April 20, 2010, 12:28:07 pm by Bern Ertl
I'm building a business application with a fairly complex database.  I wanted to implement triggers to calculate a value when one of the parameters in the formula changed.  In the process, I learned a few things that weren't explicit in the SQLite docs and I thought I'd share (because they are pretty cool and powerful).

First, I'll explain a little bit about the database layout I'm using (table => column<s>):

JobPlan => Manhour
Task => JobPlanID (index to JobPlan RowID), Duration
~~
ResourceUnits => RU_Type
Resources => RUID (index to ResourceUnits RowID), code info
ResourceAssignments => TaskID (index to Task RowID), ResID (index to Resources RowID), Quantity
~~
Scenarios => Inactive
Relationships => TaskID (index to Task RowID), ScenarioID (index to Scenario RowID - can be null)

A JobPlan can have any number of associated Tasks.  A Task can have zero or one associated Relationship.  A Relationship can have zero or one associated Scenario (which, if it exists, can indicate if the task is "active" or "inactive").  A task without a relationship to a scenario is considered active.

A Task can also have any number of Resource Assignments, which must be associated with a Resource.  A Resource may or may not be associated with a ResourceUnit (if it isn't, it's considered undefined and not included in the ManHour calculations).

To calculate the ManHour for a JobPlan, I need to sum up the Task.Duration * ResourceAssignments.Quantity for every valid Task/ResourceAssignment associated with the JobPlan.  Writing a TRIGGER to do this is complicated because of the many to one relationships and because some of the relationships are not guaranteed (ie. Relationships / Scenarios might be NULL).

When writing my first trigger, to calculate the JobPlan.Manhour field when a related Task.Duration is updated, I struggled a bit figuring out how to encompass the many to one aspect of the valid resource assignments.  Here's what I ended up with (code segments are part of a large BUILD$() statement, hence the formatting with commas and underscores):

            "CREATE TRIGGER fku_Calc_Mhs_Dur AFTER UPDATE OF CurDur ON Tasks FOR EACH ROW WHEN ", _
                                 "(SELECT Tasks.RowID FROM Tasks ", _
                                    "INNER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID ", _
                                    "INNER JOIN Scenarios ON Relationships.Scenario_ID = Scenarios.RowID ", _
                                    "WHERE Scenarios.Inactive = 1 AND Tasks.RowID = OLD.RowID) IS NULL ", _
                  "BEGIN ", _
                     "UPDATE JobPlans SET Cur_Mhs = (SELECT Total( CurDur * ", _
                                                                     "(SELECT TOTAL( ResAssignments.Quantity) FROM ResAssignments ", _
                                                                        "INNER JOIN Resources ON Resources.RowID = ResAssignments.Res_ID ", _
                                                                        "INNER JOIN ResUnits ON ResUnits.RowID = Resources.RU_Code_ID WHERE ResAssignments.Task_ID = RowID AND ", _
                                                                           "ResUnits.RU_Type = 0)) ", _
                                                    "FROM (SELECT CurDur, RowID FROM Tasks WHERE JobPlan_ID = OLD.JobPlan_ID EXCEPT SELECT Tasks.CurDur, Tasks.RowID FROM Tasks ", _
                                                                "INNER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID ", _
                                                                "INNER JOIN Scenarios ON Relationships.Scenario_ID = Scenarios.RowID ", _
                                                                "WHERE Scenarios.Inactive = 1 AND Tasks.JobPlan_ID = OLD.JobPlan_ID)) ", _
                        "WHERE RowID = OLD.JobPlan_ID; ", _
                  "END;", _


I had to make use of nested SELECT statements employing the TOTAL() aggregate function.  Additionally, I needed to use a compound SELECT with EXCEPT in the FROM portion of the main SELECT statement to obtain the correct set of valid tasks.

It took me a while to get the syntax right (not including table names all the time), but this works.

Next, I had to write a trigger to calculate the JobPlan.Manhour field when a related ResourceAssignments record is updated.  This one added a new twist - I don't have a direct reference to the JobPlan's RowID from the triggering event, so how do I limit the update to the correct record?  I ended up with:                        "WHERE RowID = (SELECT JobPlan_ID FROM Tasks WHERE Tasks.RowID = OLD.Task_ID); ", _
                  "END;", _


Finally, I needed a trigger to update the Manhours for all affected/related JobPlans associated with all affected Resources when a ResourceUnit changes type.  This one is interesting:            "CREATE TRIGGER fku_Calc_Mhs_Res_Type AFTER UPDATE OF RU_Type ON ResUnits FOR EACH ROW WHEN ", _
                                 "(SELECT ResAssignments.RowID FROM ResAssignments ", _
                                    "INNER JOIN Resources ON Resources.RowID = ResAssignments.Res_ID ", _
                                    "WHERE Resources.RU_Code_ID = OLD.RowID) IS NOT NULL ", _
                  "BEGIN ", _
                     "UPDATE JobPlans SET Cur_Mhs = (SELECT Total( CurDur * ", _
                                                                     "(SELECT TOTAL( ResAssignments.Quantity) FROM ResAssignments ", _
                                                                        "INNER JOIN Resources ON Resources.RowID = ResAssignments.Res_ID ", _
                                                                        "INNER JOIN ResUnits ON ResUnits.RowID = Resources.RU_Code_ID WHERE ResAssignments.Task_ID = RowID AND ", _
                                                                           "ResUnits.RU_Type = 0)) ", _
                                                    "FROM (SELECT CurDur, RowID FROM Tasks WHERE JobPlan_ID = JobPlans.RowID ", _
                                                             "EXCEPT SELECT Tasks.CurDur, Tasks.RowID FROM Tasks ", _
                                                                "INNER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID ", _
                                                                "INNER JOIN Scenarios ON Relationships.Scenario_ID = Scenarios.RowID ", _
                                                                "WHERE Scenarios.Inactive = 1 AND Tasks.JobPlan_ID = JobPlans.RowID)) ", _
                        "; ", _
                  "END;", _


I thought there would be a problem tying the SELECT statements that calculate the TOTALs to a specific JobPlan (RowID), but you can reference the RowID for each JobPlan in the compound SELECT in the FROM portion of the main SELECT statement.  This was not obvious or intuitive to me, but I'm glad it works.

I'm not sure that I did a good explaining the issues and solutions I encountered while writing these triggers, but these examples do show some powerful concepts that are available with triggers.