Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update master from detail Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 11:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:02 PM
Points: 320, Visits: 441
I have a timesheet master table and a related timesheet detail table. Each detail row could trigger need for an override approval of the whole timesheet. Currently, I have an ASP.Net application that checks for 1 or more detail rows needing override approval, and updates the master column "RequiresOV" (int). I'm thinking of setting a trigger on the detail table to do the update on the master automatically anytime a row requiring override permission is added, updated, or deleted. It would simply add up all the detail rows requiring Override, and update the master appropriately.

Alternatively, I could make the column in the master a computed column that does roughly the same thing.

Override rows are uncommon, say 5% of timesheets contain an override row. The average timesheet has maybe 300 rows, and there are maybe a dozen timesheets submitted per week. Some are submitted in bulk (uploaded from a workbook...) and some are hand-entered in an ASP.Net app, one row at a time.

What do you guys think?


Jim
Post #1428707
Posted Sunday, March 10, 2013 9:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
I would not update the master table but set up another "queue" table for approval requests.
Once a "details" row is updates the coresponding key value is inserted by the trigger into the "queue" table, unless it's not already there:
INSERT INTO Queue (MasterKey)
SELECT MasterKey FROM Details D
WHERE NOT EXISTS(select * from Queue Q where Q.MasterKey = D.MasterKey)

Once Approval process is completed for any particular MaskterKey it's just removed from the Queue table.

You may wish to add a "time stamp" column to the queue table with default GETDATE().
Then it will be easy to process requests in order they've been submitted.
Post #1429058
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse