I work for a security contracting company, and I have a table in my database that stores adjusted hours/times for worked POs. For each date that is worked, the finance department will enter the billable start and end time for the vendor, and the billable start and end time for the client. Most of the time, the vendor and client billable times are equal, except in rare circumstances.
The way our system works, the vendor start and end time always updates the client start and end time, unless the client start and end time differs from the vendor start and end time. To store this information, I have a table with these columns:
Since the vendor and client start and end times usually need to stay synced with each other, I figured that I would use a trigger to handle it. Because I need access to the current values to compare them against the new values, it seems that an INSTEAD OF INSERT, UPDATE trigger is the way to go, except that I'm not crazy about triggers in the first place, and relying on a trigger to perform all of my inserts and updates into this table makes me nervous. Maybe it's an irrational fear I have, but I usually try to stay away from triggers when I can. In this circumstance however, it seems like the best option.
Here is my trigger, which should make the logic clear:
ALTER TRIGGER [dbo].[<UpdateAdjustedHours>]
ON [dbo].[<AdjustedHoursTable>]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
IF (NOT EXISTS(SELECT CurrentValues.JobID FROM WorkOrderDetailAdjustment CurrentValues, Inserted NewValues WHERE CurrentValues.JobID = NewValues.JobID AND CurrentValues.PostNumber = NewValues.PostNumber))
BEGIN
INSERT INTO WorkOrderDetailAdjustment
SELECT Inserted.JobID,
Inserted.PostNumber,
Inserted.StartTime,
Inserted.ClosingTime,
ISNULL(Inserted.ClientStartTime, Inserted.StartTime),
ISNULL(Inserted.ClientClosingTime, Inserted.ClosingTime)
FROM Inserted
END
ELSE BEGIN
UPDATE CurrentValues
SET CurrentValues.StartTime = ISNULL(NewValues.StartTime, CurrentValues.StartTime),
CurrentValues.ClosingTime = ISNULL(NewValues.ClosingTime, CurrentValues.ClosingTime),
CurrentValues.ClientStartTime = (
CASE WHEN DATEDIFF(SECOND, CurrentValues.ClientStartTime, CurrentValues.StartTime) != 0 THEN
ISNULL(NewValues.ClientStartTime, CurrentValues.ClientStartTime)
ELSE
NewValues.StartTime
END
),
CurrentValues.ClientClosingTime = (
CASE WHEN DATEDIFF(SECOND, CurrentValues.ClientClosingTime, CurrentValues.ClosingTime) != 0 THEN
ISNULL(NewValues.ClientClosingTime, CurrentValues.ClientClosingTime)
ELSE
NewValues.ClosingTime
END
)
FROM WorkOrderDetailAdjustment CurrentValues, Inserted NewValues
WHERE CurrentValues.JobID = NewValues.JobID AND CurrentValues.PostNumber = NewValues.PostNumber
END
END
What I'm wondering is if there's a better way to do this. I'm open to all suggestions, but if possible I would like to keep this at the database level. I'm also open to hearing that I chose the best possible way of doing this, but for some reason I doubt it.
Thank you for your help!
One thing you could do...if it makes sense for your operation, is to add a persisted computed column to your table. (I'm inferring from your description that you actually want this value stored. If not, you don't need the persisted option).
It could be something like coalesce(client_start_time, vender_start_time).
Alternatively, you could just create view that did the same thing. I'm not crazy about views, as they're easy to abuse, but they're not always bad.
Eithe rway, you'd only have to enter client_start time when it was actually different than vender_start_time. And you could avoid using a trigger.
I would suggest handling it in business logic layer of your application and not in the database.
We went through a painful time of having business rules placed "everywhere" - in application back end, in database in stored procedures and triggers. We redesigned our approach and split the applications in strict layers. Development and maintenance is much smoother now. In my opinion the task you described - keeping the billable data synchronized - is part of the business logic and should be implemented there.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With