CODEWITHSUNDEEP
sqlsql-servertriggers
Adrian
Adrian

Reputation: 346

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

Upvotes: 0

Views: 57

Answers (1)

StackUser
StackUser

Reputation: 5398

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

Upvotes: 1

Related Questions