Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Unknown Unknown » 10/22
Add to briefcase

10/22 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Site Owners
Posted Saturday, October 20, 2007 2:18 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,040, Visits: 1
Comments posted to this topic are about the item 10/22
Post #413109
Jeff Moden
Posted Saturday, October 20, 2007 3:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800
Although 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.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #413112
matt stockham
Posted Monday, October 22, 2007 11:50 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: Thursday, March 14, 2013 10:22 AM
Points: 750, Visits: 2,937
Apparently selecting A is incorrect, because the correct answer is A or B .........
Post #413512
Lynn Pettis
Posted Monday, October 22, 2007 12:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 21,624, Visits: 27,465
Here 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.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #413518
rodjkidd
Posted Wednesday, October 24, 2007 5:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 2,465, Visits: 3,588
Hi,
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...


Post #414325
Jeff Moden
Posted Wednesday, October 24, 2007 7:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800
I'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.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #414719
Yelena Varshal
Posted Friday, November 02, 2007 8:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519
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.
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.

Regards,
Yelena Varshal
Post #417979
scout
Posted Thursday, November 15, 2007 2:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2008 1:51 AM
Points: 320, Visits: 8
Yelena Varshal (11/2/2007)
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.


Right! "a or b" means both "a" and "b" are valid, but "a" is not (as of BOL).


Post #422504
nigelrivett
Posted Thursday, November 15, 2007 8:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, January 28, 2013 1:45 AM
Points: 386, Visits: 199
Correct 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.


Cursors never.
DTS - only when needed and never to control.
Post #422695
L' Eomot Inversé
Posted Tuesday, April 20, 2010 4:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:11 PM
Points: 7,104, Visits: 7,168
Yelena Varshal (11/2/2007)
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.

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.

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #907317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.