IF UPDATE() in SQL server trigger

If there's:

IF UPDATE (col1)

...in the SQL server trigger on a table, does it return true only if col1 has been changed or been updated?

I have a 开发者_开发问答regular update query like

UPDATE table-name 
   SET col1 = 'x', 
       col2 =  'y' 
 WHERE id = 999

Now what my concern is if the "col1" was 'x' previously then again we updated it to 'x' would IF UPDATE ("col1") trigger return True or not?

I am facing this problem as my save query is generic for all columns, but when I add this condition it returns True even if it's not changed...So I am concerned what to do in this case if I want to add condition like that?

It returns true if a column was updated. An update means that the query has SET the value of the column. Whether the previous value was the same as the new value is largely irelevant.

UPDATE table SET col = col

it's an update.

UPDATE table SET col = 99

when the col already had value 99 also it's an update.

Within the trigger, you have access to two internal tables that may help. The 'inserted' table includes the new version of each affected row, The 'deleted' table includes the original version of each row. You can compare the values in these tables to see if your field value was actually changed.

Here's a quick way to scan the rows to see if ANY column changed before deciding to run the contents of a trigger. This can be useful for example when you want to write a history record, but you don't want to do it if nothing really changed.

We use this all the time in ETL importing processes where we may re-import data but if nothing really changed in the source file we don't want to create a new history record.

CREATE TRIGGER [dbo].[TR_my_table_create_history]
ON [dbo].[my_table] FOR UPDATE AS

BEGIN

    --
    -- Insert the old data row if any column data changed
    --
    INSERT INTO [my_table_history]
    SELECT  d.*
    FROM    deleted d
    INNER JOIN inserted i ON i.[id] = d.[id]
    --
    -- Use INTERSECT to see if anything REALLY changed
    --
    WHERE   NOT EXISTS( SELECT i.* INTERSECT SELECT d.* )

END

Note that this particular trigger assumes that your source table (the one triggering the trigger) and the history table have identical column layouts.

What you do is check for different values in the inserted and deleted tables rather than use updated() (Don't forget to account for nulls). Or you could stop doing unneeded updates.

Trigger:

CREATE TRIGGER boo  ON status2 FOR UPDATE AS 
IF UPDATE (id)
BEGIN
   SELECT 'DETECT'; 
END;

Usage:

UPDATE status2 SET name = 'K' WHERE name= 'T' --no action 
UPDATE status2 SET name = 'T' ,id= 8 WHERE name= 'K' --detect

To shortcut the "No actual update" case, you need also check at the beginning whether your query affected any rows at all:

set nocount on; -- this must be the first statement!
if not exists (select 1 from inserted) and not exists (select 1 from deleted)
  return;

SET NOCOUNT ON;

    declare @countTemp int
    select @countTemp = Count (*) from (
    select City,PostCode,Street,CountryId,Address1 from Deleted
    union
    select City,PostCode,Street,CountryId,Address1 from Inserted
    ) tempTable

    IF ( @countTemp > 1 )

Begin

-- Your Code goes Here
End

-- if any of these  "City,PostCode,Street,CountryId,Address1" got updated then trigger

-- will  work in " IF ( @countTemp > 1 ) " Code)

This worked for me

DECLARE @LongDescDirty bit = 0

Declare @old varchar(4000) = (SELECT LongDescription from deleted)
Declare @new varchar(4000) = (SELECT LongDescription from inserted)

if (@old <> @new)
    BEGIN
        SET @LongDescDirty = 1
    END

Update table
  Set LongDescUpdated = @LongDescUpdated

.....