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

10/22 Expand / Collapse
Author
Message
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,039, Visits: 1
Comments posted to this topic are about the item 10/22
Post #413109
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 @ 4:58 PM
Points: 37,107, Visits: 31,664
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #413112
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: Tuesday, September 16, 2014 6:34 PM
Points: 750, Visits: 3,159
Apparently selecting A is incorrect, because the correct answer is A or B .........
Post #413512
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 @ 4:56 PM
Points: 23,400, Visits: 32,256
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
Posted Wednesday, October 24, 2007 5:01 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: Today @ 6:27 AM
Points: 3,006, Visits: 5,474
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
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 @ 4:58 PM
Points: 37,107, Visits: 31,664
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #414719
Posted Friday, November 2, 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, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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
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
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: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
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
Posted Tuesday, April 20, 2010 4:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 8,844, Visits: 9,405
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
Post #907317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse