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.
The
UPDATE()
function will return1
even if the update/insert statement fails, so you probably don't want to use that as an indicator.You have no reference to the
inserted
or to thedeleted
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:
By joining the
inserted
anddeleted
tables, I'm ensuring the trigger only changes the rows effected by the statement that fired it.Change
<PKColumn(s)>
to the column(s) that makes up the primary key of the table.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 tovarchar(11)
and replacenull
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
How to Insert Null into the Datetime Coulmn Instead 1900-01-01 00:00:00.000 in SQL Server
How to Execute a Stored Procedure Once for Each Row Returned by Query
How to Use Select Distinct and Concat in the Same SQL Statement
T-Sql Challenges from Hackerrank
How to Get Column Name Based on Row Value in SQL Server
Query to Get All Those Names of Employees,Who Have 'A' as Their Middle Character in Their Name
How to Close Idle Connections in Postgresql Automatically
How to Display Half Records from Table - MySQL
How to Divide Sum in Number from Count in Having
Finding the Highest Average Salary in SQL
Insert Value into Table If Conidition Is Met At Least One Time
What's the Best Way to Capitalise the First Letter of Each Word in a String in SQL Server
How to Merge Rows on Specific Condition
Checking a Column If It Contains a Row Value
Sequelize Connect Etimeout When Connecting to Remote MySQL Db
Postgresql Convert Columns to Rows Transpose
Total Sum of Multiple Columns in Oracle SQL Statement by Unique Id