Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

UPDATE() and triggers Expand / Collapse
Author
Message
Posted Wednesday, December 02, 2009 10:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:52 AM
Points: 2,919, Visits: 2,511
SJ - I believe that you are correct. SQL Server is not smart enough to tell you whether the value changed, but that there was an update that did not fail. Thus, the update completed and the trigger fires. It leaves the smarts to the developer to determine ir check and see if the value changed or not.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #827683
Posted Wednesday, December 02, 2009 1:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,794, Visits: 8,009
Sjimmo and Steve,

It's like you say, but then it goes even a step further.

The UPDATE() function does, as you indicate, not check if any values actually changed.
But it also doe not check if any values were set to some (possibly unchanged) value.

The only thing UPDATE() tells you is that the column is mentioned at the left-hand side of an =-mark in the SET phrase. So even if the table is empty, the WHERE does not match any rows, or even if the WHERE has an always false predicate, the UPDATE() function will still return TRUE for all columns that appear in the SET clause.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #827747
Posted Wednesday, December 02, 2009 3:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:56 PM
Points: 522, Visits: 553
Well, not quite every column that appears in the set line.. the trigger update function is restricted to a single column at a time, but you can chain them together :)
Post #827827
Posted Wednesday, December 02, 2009 4:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
Great question. It took me thorugh several layers. I had to realize that the trigger could never reach the "did not fire" branch of the if statement. Then, I was fooled (as were many, apparently) by the notion that a null-change would suppress the trigger. But then, a bit of experimentation led to deeper understanding.

Q: If it "didn't fire", how would it be running the IF statement?
A: Only if there were another column to be affected. This code does return "The Trigger did not fire" even though it did because the VALUE column was not touched.
-- See a trigger say that it didn't fire....
-- (actually, the trigger fires, but internally it sees
-- that the tested field VALUE wasn't affected)
CREATE TABLE TriggerTest (Value int, Text char(10))
GO
INSERT INTO TriggerTest VALUES (1, 'Hello')
GO

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

Second, and to the point of several posts here, the trigger fires upon "an attempt" to update the table, but determining what constitutes an update attempt is a bit tricky. Microsoft's documentation is slightly misleading when it says in the link provided:
UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

Actually, it fires even if no rows are found to update, but an error will prevent it from firing, so an unsucessful attempt in that case won't return TRUE. By example, this code returns "The trigger fired" even though it effects no change to the database (not even a wash of updating a field to its original value):
--See trigger fire with 0 rows affected
UPDATE TriggerTest SET Value = Value
where value = 7

...but if there's an error, as forced in the following statement, SQL Server doesn't consider the update to have been attempted and so does not fire the trigger.
--See an error prevent the update and thereby the trigger
UPDATE TriggerTest SET Value = 'Value'

----
edit: minor typo
Post #827857
Posted Wednesday, December 09, 2009 2:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, June 21, 2010 12:30 AM
Points: 798, Visits: 87
I also get the error:
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Value'.
Post #831231
Posted Wednesday, December 09, 2009 3:54 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
was tricky ... but i got it



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #831274
Posted Wednesday, December 09, 2009 4:02 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, June 21, 2010 12:30 AM
Points: 798, Visits: 87
How did u get it?
Post #831278
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse