What can I use instead of an update trigger?

Question

I have an update trigger in SQL Server and I want to remove this trigger and make update operation with a stored procedure instead of the trigger. But I have UPDATE(end_date) control in update trigger.

What can I use instead of below UPDATE(end_date) control? How can I compare old and new end_dates in stored procedure efficiently?

Update trigger

ALTER TRIGGER [dbo].[trig_tbl_personnel_car_update] 
ON [dbo].[tbl_personnel_cars]
FOR UPDATE 
AS
    IF (UPDATE(end_date))
        UPDATE pc
        SET pc.owner_changed = 1
        FROM tbl_personnel_cars pc, inserted i
        WHERE pc.pk_id = i.pk_id

Sample updated script in stored procedure

ALTER PROCEDURE [dbo].[personnel_car_update]   
    (@PkId INT)
    UPDATE tbl_personnel_cars 
    SET end_date = GETDATE()
    WHERE pk_id = @PkId

I update tbl_personnel_cars table inside many stored procedures like this. How can I update this table like trigger does instead of update trigger?

I tried below codes to get old and new end_dates but I can't.

Sample updated script in stored procedure:

ALTER PROCEDURE [dbo].[personnel_car_update]   
    (@PkId INT)
    UPDATE tbl_personnel_cars 
    SET end_date = GETDATE()
    WHERE pk_id = @PkId

EXEC update_operation_sp_instead_trigger @PkId

ALTER PROCEDURE [dbo].[update_operation_sp_instead_trigger]   
    (@PkId INT)
    UPDATE pc
    SET pc.owner_changed = 1
    FROM tbl_personnel_cars pc
    JOIN tbl_personnel_cars pc2 ON pc.pk_id = pc2.pk_id
    WHERE pc.end_date <> pc2.end_date

And last question. Is it a correct choice to use stored procedure instead of trigger where the table is updated?

Answer 1

Firstly, I want to clarify a misunderstanding you appear to have about the UPDATE function in Triggers. UPDATE returns a boolean result based on if the column inside the function was assigned a value in the SET clause of the UPDATE statement. It does not check if that value changed. This is both documented feature, and is stated to be "by-design".

This means that if you had a TRIGGER with UPDATE(SomeColumn) the function would return TRUE for both of these statements, even though no data was changed:

UPDATE dbo.SomeTable
SET SomeColumn = SomeColumn;

UPDATE ST
SET SomeColumn = NULL
FROM dbo.SomeTable ST
WHERE SomeColumn IS NULL;

If, within a TRIGGER, you need to check if a value has changed you need to reference the inserted and deleted pseudo-tables. For non-NULLable columns equality (=) can be checked, however, for NULLable columns you'll also need to check if the column changed from/to NULL. In the latest version of the data engine (at time of writing) IS DISTINCT FROM makes this far easier.

---

Now onto the problem you are actually trying to solve. It looks like you are, in truth, overly complicated this. Firstly, you are setting the value to GETDATE so it is almost certainly impossible that the column will be set to be the same value it already set to; you have a 1/300 second window to do the same UPDATE twice, and if you add IO operations, connection timing, etc, that basically makes hitting that window twice impossible.

For what you want, just UPDATE both columns in your procedure's definition:

ALTER PROCEDURE [dbo].[personnel_car_update] @PkId int AS --This has a trailing comma, which is invalid syntax. The parathesis are also not needed; SP's aren't functions. You were also missing the AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.tbl_personnel_cars  --Always schema qualify
    SET end_date = GETDATE(),
        owner_changed = 1
    WHERE pk_id = @PkId;

END;