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.
fyi The code I ended up with:
IF UPDATE (QtyToRepair)
begin
INSERT INTO tmpQtyToRepairChanges (OrderNo, PartNumber, ModifiedDate, ModifiedUser, ModifiedHost, QtyToRepairOld, QtyToRepairNew)
SELECT S.OrderNo, S.PartNumber, GETDATE(), SUSER_NAME(), HOST_NAME(), D.QtyToRepair, I.QtyToRepair 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 I.QtyToRepair <> D.QtyToRepair
end
One should check if QtyToRepair
is updated at first.
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
You want to do the following:
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
Please note that this trigger will fire each time you update the column no matter if the value is the same or not.