InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 08:54:07 GMT20http://www.sqlservercentral.com/Forums/Topic413109-34-1.aspx[quote][b]Yelena Varshal (11/2/2007)[/b][hr]I think that UPDATE() is incorrect answer.The question was:"Within a trigger, how can you tell if a value has altered on a specific column? "The explanations for the UPDATE() in BOL are:"Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. "First, as other posters pointed here, the attempt does not mean the update was successful.[/quote]That's rather obvious, as you are in the trigger, so the update staement has not yet terminated - if it's an "instead of" trigger it can decide not to do the update, even if it's an "after" trigger it can choose to roll back the transaction, multiple triggers may be firing in sequence and one of those after you in teh sequence can do this. It's completely meaningless to ask, in th etrigger, whether tyhe update was successful.[quote ]Second: the question is asking about the value being altered, not about the value being inserted.[/quote]If you need to know whether the action is an uodate or an insert you will presumably not have the same trigger for both.Tue, 20 Apr 2010 16:23:39 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxCorrect answer would be none of the above - as that's not an option a, b is obviously what's wanted.You need to guess the answer required rather than what is correct.It's a common problem with closed questions.Thu, 15 Nov 2007 08:47:51 GMTnigelrivetthttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspx[quote][b]Yelena Varshal (11/2/2007)[/b][hr]I think that UPDATE() is incorrect answer.The question was:"Within a trigger, how can you tell if a value has altered on a specific column? "The explanations for the UPDATE() in BOL are:"Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. "First, as other posters pointed here, the attempt does not mean the update was successful.[/quote]Right! "a or b" means both "a" and "b" are valid, but "a" is not (as of BOL).Thu, 15 Nov 2007 02:20:01 GMTscouthttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxI think that UPDATE() is incorrect answer.The question was:"Within a trigger, how can you tell if a value has altered on a specific column? "The explanations for the UPDATE() in BOL are:"Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. "First, as other posters pointed here, the attempt does not mean the update was successful.Second: the question is asking about the value being altered, not about the value being inserted.So you can not tell by this function if the value was altered, or new value inserted or just attempts were made.Fri, 02 Nov 2007 08:56:50 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxI'm not so worried about me reading into such questions... I'm more worried that someone will take the answer as the gospel and mess up their data when they actually go to use it by thinking that it will detect changes. They could have at least put the required comparison between INSERTED and DELETED in the answer... a more clever question might have had a "None of the above" as the 5th selection.Wed, 24 Oct 2007 19:41:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxHi,I agree with Lynn.I'm pretty sure the idea is to introduce you to the UPDATE(Columnname) and COLUMS_UPDATED.I did a double take on the UPDATED table answer as I would compare the deleted and inserted tables... I've been caught out before where I've speed read the possible answers and assumed what the question was getting at! Or made up my own version of the possible answers...I once got asked in an interview about the UPDATED table in a trigger and then spent nearly 10 minutes explaining there isn't an UPDATED table in triggers... The guy asking the question didn't set it! It goes without saying I didn't take the job!I guess the UPDATE() is a quick and dirty way to see if a column was updated, but you would still need to compare the old and new values to see if it actually changed.Rodders...Wed, 24 Oct 2007 05:01:10 GMTrodjkiddhttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxHere is my take on things; don't read too much into the questions. Although the correct answer is a or b (either a or b alone is only partially correct based on BOL), in the real world must of us would also do a comparision between inserted and deleted to be sure an actual update occurred.Mon, 22 Oct 2007 12:02:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxApparently selecting A is incorrect, because the correct answer is A or B .........Mon, 22 Oct 2007 11:50:26 GMTmatt stockhamhttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxAlthough I got the answer right, it's kind of misleading... if the value of a column has been updated to the same value (usually do to bad GUI code), do you want, say, an audit trigger to still fire? It will if you only use the correct answer... if you don't want it to, you must also compare the values from the two trigger tables, taking null into consideration, to see if the value actually changed.Sat, 20 Oct 2007 15:29:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic413109-34-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/61354/">10/22</A>[/B]Sat, 20 Oct 2007 14:18:22 GMTSite Owners