Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


10/22


10/22

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

Group: General Forum Members
Points: 10095 Visits: 1
Comments posted to this topic are about the item 10/22
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45253 Visits: 39934
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
matt stockham
matt stockham
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

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

Group: General Forum Members
Points: 24255 Visits: 37978
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3660 Visits: 8037
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45253 Visits: 39934
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593
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 (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

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



nigelrivett
nigelrivett
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10761 Visits: 12019
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