Adrian Reszka Adrian Reszka - 4 months ago 8
SQL Question

Trigger update of a table only if the value was actually changed

Is there a way to create a trigger that would update the table only if the actual data has been changed?

Below you can see my trigger, which will trigger if

idu_id
or
manager_id
gets updated. But, if you force the change to take place like 1 changed to 1, it still triggers even though the updated table will be unchanged.

ALTER TRIGGER [dbo].[TRG_UpdatePersonal]
ON [dbo].[HC_EMP]
FOR UPDATE AS IF ( UPDATE([manager_id]) OR UPDATE([idu_id]))

DECLARE @user_login VARCHAR(50)
DECLARE @Action varchar (10)
SELECT @user_login = dbo.udf_GetUserLogin()

BEGIN
IF UPDATE (idu_id)
Insert into [dbo].[HC_HISTORY]
(

[EmpID]
,[ACTIONDATE]
,[TYPEID]
,[MESSAGE]
,[InitiatorID]
,[OldIdu]
,[NewIdu]
)
select

d.[company_id]
,GETUTCDATE()
,3
,'Some Data'
,@user_login
,d.idu_id
,i.idu_id
from Deleted d join INSERTED i on d.company_id = i.company_id

IF UPDATE (manager_id)
Insert into [dbo].[HC_HISTORY]
(
[EmpID]
,[ACTIONDATE]
,[TYPEID]
,[MESSAGE]
,[InitiatorID]
,[OldManagerId]
,[NewManagerId]
)
select

d.[company_id]
,GETUTCDATE()
,4
,'Some Other Data'
,@user_login
,d.manager_id
,i.manager_id
from Deleted d join INSERTED i on d.company_id = i.company_id
END

Answer

Add a filter in your where clause. Just try like this,

ALTER TRIGGER [dbo].[TRG_UpdatePersonal] ON [dbo].[HC_EMP]
FOR UPDATE
AS
IF (
        UPDATE ([manager_id])
            OR
        UPDATE ([idu_id])
        )
    DECLARE @user_login VARCHAR(50)
DECLARE @Action VARCHAR(10)

SELECT @user_login = dbo.udf_GetUserLogin()

BEGIN
    INSERT INTO [dbo].[HC_HISTORY] (
        [EmpID]
        ,[ACTIONDATE]
        ,[TYPEID]
        ,[MESSAGE]
        ,[InitiatorID]
        ,[OldIdu]
        ,[NewIdu]
        )
    SELECT d.[company_id]
        ,GETUTCDATE()
        ,3
        ,'Some Data'
        ,@user_login
        ,d.idu_id
        ,i.idu_id
    FROM Deleted d
    JOIN INSERTED i ON d.company_id = i.company_id
    WHERE d.idu_id <> i.idu_id

    INSERT INTO [dbo].[HC_HISTORY] (
        [EmpID]
        ,[ACTIONDATE]
        ,[TYPEID]
        ,[MESSAGE]
        ,[InitiatorID]
        ,[OldManagerId]
        ,[NewManagerId]
        )
    SELECT d.[company_id]
        ,GETUTCDATE()
        ,4
        ,'Some Other Data'
        ,@user_login
        ,d.manager_id
        ,i.manager_id
    FROM Deleted d
    JOIN INSERTED i ON d.company_id = i.company_id
    WHERE d.manager_id <> i.manager_id
END
Comments