Sql Update Trigger Only When Column Is Modified

SQL update trigger only when column is modified

You have two way for your question :

1- Use Update Command in your Trigger.

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (QtyToRepair) 
    BEGIN
        UPDATE SCHEDULE 
        SET modified = GETDATE()
           , ModifiedUser = SUSER_NAME()
           , ModifiedHost = HOST_NAME()
        FROM SCHEDULE S INNER JOIN Inserted I 
        ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
        WHERE S.QtyToRepair <> I.QtyToRepair
    END 
END

2- Use Join between Inserted table and deleted table

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;    

    UPDATE SCHEDULE 
    SET modified = GETDATE()
       , ModifiedUser = SUSER_NAME()
       , ModifiedHost = HOST_NAME()
    FROM SCHEDULE S 
    INNER JOIN Inserted I ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
    INNER JOIN Deleted D ON S.OrderNo = D.OrderNo and S.PartNumber = D.PartNumber                  
    WHERE S.QtyToRepair <> I.QtyToRepair
    AND D.QtyToRepair <> I.QtyToRepair
END

When you use update command for table SCHEDULE and Set QtyToRepair Column to new value, if new value equal to old value in one or multi row, solution 1 update all updated row in Schedule table but solution 2 update only schedule rows that old value not equal to new value.

SQL Server trigger on specific column updated

thanks to Mr. Bhosale

USE [lms_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[updateLM_TIME]
ON [dbo].[INSTITUTIONS]

AFTER INSERT, UPDATE 
AS

IF NOT UPDATE(CLIENT_SYNCED_TIME)

BEGIN

UPDATE dbo.INSTITUTIONS
SET lm_time = CONVERT(DATETIME, CONVERT(VARCHAR(20), GETDATE(), 120))
WHERE ID IN (SELECT DISTINCT ID FROM Inserted)

END

Update one column in a trigger after updating only particular column in the same table in SQL Server 2008

You just needed to add an IF UPDATE check and join deleted table in your trigger:

CREATE TRIGGER [dbo].[TransactionDateUpdate]
ON [dbo].[Tasks]
AFTER UPDATE  
AS 
BEGIN
   IF UPDATE(acceptanceState)  --add this line
   UPDATE dbo.Tasks
   SET TransactionDateTime = GETDATE()
   FROM INSERTED i
   JOIN DELETED d on i.TasksID = d.TasksID --add this line
   WHERE i.TasksID = Tasks.TasksID 
END

SQL Server trigger to check if column value was changed to insert

You can use the deleted table to find out what was the old value and inserted for the new value. Also note that INSERT, UPDATE and DELETE may affect more than one rows, so you trigger should handled that.

below trigger code will gives you list of rows for any change in deptNotes.

select  'deptActivityID ' + d.deptActivityID +
        ' Changed from ' + d.deptNotes + ' to ' + i.deptNotes
from    deleted d
        inner join inserted i   on  d.deptActivityID    = i.deptActivityID
where   d.deptNotes <> i.deptNotes

If your deptNotes may contains NULL value, you need to use ISNULL() to handle that

SQLite "modified" trigger which only only updates modified field if values have actually changed

Instead of the operator != use the operator IS NOT which works when one or both of the operands are NULL:

......................................................
WHERE (OLD.order_id=NEW.order_id) 
  AND (
       (OLD.order_date IS NOT NEW.order_date) OR 
       (OLD.grand_total_in_dollars IS NOT NEW.grand_total_in_dollars) OR 
       (OLD.ship_to IS NOT NEW.ship_to) OR 
       (OLD.store IS NOT NEW.store) OR 
       (OLD.more_href IS NOT NEW.more_href) OR
       (OLD.grand_total IS NOT NEW.grand_total)
      );

Find modified fields in Update Trigger

Unfortunately I'm not aware of any reliable built-in function that can give you an indication if the value of a column actually changed when an update statement fired a trigger.

However, there is pretty simple select statement you can execute to see what values have changed in what rows:

SELECT IIF(ISNULL(NULLIF(I.Col, D.Col), NULLIF(D.Col, I.Col)) IS NULL, 0, 1) As Col_Updated
FROM Inserted I
JOIN Deleted D
    ON I.PrimaryKey = D.PrimaryKey

The NULLIF function will return null if both columns are equal. If not, it returns the value of the first column.

The ISNULL function will return the first argument that is not null, or null if both arguments are null.

Using ISNULL on the result of two NULLIF functions where the column values are reversed will result in null if the values of both columns are the same, even if they both are null. If the values are different, even if one of them is null and the other is not, then ISNULL will return a value. Therefor, all you have to do is check if the ISNULL returned a value or null - if it return null, you know the column's value was not changed. If it returns a value, then it is changed.

Of course, for non-nullable columns you can simplify the condition like this:

SELECT IIF(I.Col <> D.Col, 1, 0) As Col_Updated
FROM...

However I would recommend against that, since if someone will change the column to allow null, the trigger code will have to change to support that - and in my experience, that's a bug waiting to happen - trigger code will probably not be changed, leading to false-negatives.

You can see a live demo on rextester.

Update

Another simple method you can use is this:

IIF((I.Col IS NULL AND D.Col IS NULL) OR (I.Col = D.Col), 0, 1) as IsUpdated.

This method relies on the fact that comparing null to anything else will result with unknown which is basically equivalent to false.

It's slightly more readable IMHO.

SQL Trigger for reverting an update change on a table only when another column has specific values

The trigger will be fired whenever an update statement is executed on the table. This can not be controlled (except disabling the trigger entirely).

You can, however, write it for better performance.

1. The UPDATE() function will return 1 even if the update/insert statement fails, so you probably don't want to use that as an indicator.

2. You have no reference to the inserted or to the deleted tables in your trigger, meaning it might effect records not included in the original update statement that triggers it.

I would probably write that trigger like this:

CREATE TRIGGER [dbo].[GrdFelde_UTrig_Custom] ON [dbo].[GrdFelde] 
FOR UPDATE AS
SET NOCOUNT ON

    UPDATE t
    SET GrdInhalt = 0
    FROM GrdFelde t
    JOIN INSERTED i ON t.<PKColumn(s)> = i.<PKColumn(s)>
    JOIN DELETED d  ON t.<PKColumn(s)> = d.<PKColumn(s)>
    WHERE t.GrdNummer LIKE 'BEST[A-Z][A-Z]%2'
    AND t.GrdInhalt <> 0
    AND ISNULL(CAST(i.GrdInhalt AS INT), -1) <> ISNULL(CAST(d.GrdInhalt AS INT), -1)

GO

Please note:

1. By joining the inserted and deleted tables, I'm ensuring the trigger only changes the rows effected by the statement that fired it.

2. Change <PKColumn(s)> to the column(s) that makes up the primary key of the table.

3. I'm casting to int and specifing -1 for null values to handle the case of change from null to a value or from a value to null. If your column is already an int, then the cast is redundant. If -1 is a valid value, you might want to consider casting to varchar(11) and replace null with an empty string.

Trigger for rows where specific column was changed

You need to compare the inserted and deleted tables, joining by primary key, and verify the results are different.

Note that the UPDATE() function only tells you if the column was present in the UPDATE, not if the value actually changed.

CREATE TRIGGER testTrigger
ON table
AFTER UPDATE
AS

  SET NOCOUNT ON;

  IF(UPDATE(KeyProperty))
  BEGIN
    UPDATE t
    SET Flag = True
    FROM (
        SELECT i.Id, i.KeyProperty
        FROM inserted i
        EXCEPT
        SELECT d.Id, d.KeyProperty
        FROM deleted d
    ) i
    JOIN table t ON t.Id = i.Id
  END

GO

---

Related Topics