What can i use instead of update trigger?-CodePudding

I have a update trigger in Sql Server and I want to remove this trigger and make update operation with store procedure instead of 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 store 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 store 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 store procedure like above.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 cant.

Sample Updated Script in store 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 store procedure instead of Trigger where the table is updated?

CodePudding user response：

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;

CodePudding user response：

Larnu gave you a great answer about the stored procedure logic, so i wanna answer your question about "Is it a correct choice to use store procedure instead of Trigger where the table is updated?"

The upsides of DML triggers are following in my opinion:

When you have a lot of places that manipulate a table, and there need to be some common logic performed together with this manipulation like audit / logging, trigger can solve it nicely because you don't have to repeat your code in a lot of places
Triggers can prevent "stupid" actions like: DELETEs / UPDATEs without WHERE by performing some specific validation etc. They can also make sure you are setting all mandatory fields (for example change date) when performing adhoc updates
They can simplify quick and dirty patches when you can concentrate your logic to one place instead of X number of procedures / compiled code.

Downside of triggers is performance in some cases, as well as some more specific problems like output clause not working on the triggered tables and more complicated maintenance.

It's seldom you can't solve these issues with other non-trigger solutions, so i'd say if your shop already uses triggers, fine, but if they don't, then there's seldom a really good reason to start either