Better alternative to INSTEAD OF INSERT, UPDATE trigger

Question

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:

• JobID
• PostNumber
• StartTime (vendor start time)
• ClosingTime (vendor start time)
• ClientStartTime
• ClientClosingTime

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!

Answer 1

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.

Answer 2

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.