SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


10/22


10/22

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Administrators
Points: 14777 Visits: 110
Comments posted to this topic are about the item 10/22
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)

Group: General Forum Members
Points: 335374 Visits: 42578
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
matt stockham
matt stockham
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3968 Visits: 3178
Apparently selecting A is incorrect, because the correct answer is A or B .........
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148716 Visits: 39263
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.

Cool
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)
rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7662 Visits: 8736
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...



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)SSC Guru (335K reputation)

Group: General Forum Members
Points: 335374 Visits: 42578
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Yelena Varshal
Yelena Varshal
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13324 Visits: 605
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

scout
scout
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 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).



nigelrivett
nigelrivett
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1654 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.

Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35712 Visits: 12784
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search