anil.kubireddi (12/2/2009)

---

i am refferning to the below question posted,

What is the output of the following code (disregarding "X row(s) affected"):

CREATE TABLE TriggerTest (Value int)
GO
INSERT INTO TriggerTest VALUES (1)
GO

CREATE TRIGGER tr_TriggerTest ON TriggerTest AFTER UPDATEAS BEGIN SET NOCOUNT ON; IF UPDATE(Value) PRINT 'The trigger fired' ELSE PRINT 'The trigger did not fire'END
GO

--Set Value equal to itself
UPDATE TriggerTestSET Value = Value;

DROP TABLE TriggerTest;


=======================

I have tried this and got fallowing error,

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Value' to a column of data type int.

it is said that the trigger gets fired, then why 'The trigger fired' statement doesn't got printed.

vkirkpat (12/2/2009)

---

But, the update DOES change the value, right? Even though it may be the same value, it does get updated. It's not like SQL Server says to itself, hey, this value is the same as then one that's already there, so don't even mess with the physical I/O's here.

anil.kubireddi (12/2/2009)

---

I have tried this and got fallowing error,

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Value' to a column of data type int.

it is said that the trigger gets fired, then why 'The trigger fired' statement doesn't got printed.

Tao Klerks (12/2/2009)

---

Right - but even if SQL Server was not doing any update at all (if there were no rows in the table, or if the WHERE clause excluded all rows), it would still consider that the column was updated.

UPDATE TriggerTest
SET Value = VALUE
WHERE VALUE = 0;

CREATE TABLE TriggerTest (Value int)
GO
INSERT INTO TriggerTest VALUES (1)
GO

CREATE TRIGGER tr_TriggerTest
   ON TriggerTest
   AFTER UPDATE
AS 
BEGIN
   SET NOCOUNT ON;
	
   PRINT 'The Trigger Fired'

   IF UPDATE(Value)
   BEGIN
      PRINT 'The Value column was updated'
      select * FROM INSERTED
   END
   ELSE
   BEGIN
      PRINT 'The Value column was not updated'
   END
END
GO

--Set Value equal to itself
UPDATE TriggerTest
SET Value = VALUE
WHERE VALUE = 0;

DROP TABLE TriggerTest;