April 9, 2013 at 4:48 am
Good question Vinay ans as usual gr8 explanation from Hugo 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 9, 2013 at 6:29 am
kapil_kk (4/9/2013)
Good question Vinay ans as usual gr8 explanation from Hugo 🙂
🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
April 9, 2013 at 7:28 am
Interesting question. Unfortunately it is testing our knowledge of a deprecated datatype and its usage in a trigger. Like several others I managed to get it right but not necessarily for the right reason.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2013 at 7:29 am
Arrrr, ya got me. I fell for the logic error and completely missed the TEXT data type.
:blink::blink:
April 9, 2013 at 8:23 am
sestell1 (4/9/2013)
Arrrr, ya got me. I fell for the logic error and completely missed the TEXT data type.:blink::blink:
Me too! Good question!
April 9, 2013 at 8:31 am
Sean Lange (4/9/2013)
Interesting question. Unfortunately it is testing our knowledge of a deprecated datatype and its usage in a trigger. Like several others I managed to get it right but not necessarily for the right reason.
The use of the deprecated TEXT datatype caused my Spidey senses to tingle enough that I did a quick Google search on the TEXT datatype and triggers, which steered me in the right direction.
Jason Wolfkill
April 9, 2013 at 9:11 am
sestell1 (4/9/2013)
Arrrr, ya got me. I fell for the logic error and completely missed the TEXT data type.:blink::blink:
I recently corrected an error in a trigger code thats using COUNT(*) = 1 logic to COUNT(*) >= 1 since it overlooks the possibility of multiple rows. But then I also wanted to include something else with it and so the text datatype.
As Hugo and others have pointed out, the catch is too misleading for a simple question like this. Although I admit that it is completely intended, I am sorry that it turned out like this.
For a second, I almost forgot about my own question and was about to answer 3. Luckily got reminded of the TEXT datatype issue at the last minute. I guess I would have been the first person to answer my own question wrong.. :w00t:
That's when I realized how silly may be the question was. Anyway, this is just my 2nd question.. I hope to improve the question quality with time..
Really like to hear what Tom has to say on this Qotd...
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
April 9, 2013 at 9:31 am
venkat9.sql (4/9/2013)
I recently corrected an error in a trigger code thats using COUNT(*) = 1 logic to COUNT(*) >= 1 since it overlooks the possibility of multiple rows.
Aarghh! Please change it again, this time to use EXISTS instead of COUNT(*) > 1.
(Unless you really want to wait while SQL Server counts all 32,957,352 violations of your business rule before rolling back the transaction?)
By the way, your question (and the number of people picking the various options) inspired me to submit a question of my own about triggers. And then, while typing, I got inspirration for yet another one. So if, in the near future, you see two questions that are a bit like this one - it's no coincidence! 😉
Thanks for inspiring me!
April 9, 2013 at 10:24 am
Hugo Kornelis (4/9/2013)
I'm glad I double and triple checked before replying; I almost fell for it. The glaring error in how the trigger code handles multi-row inserts is so staring people in the eye that it distracts from the actual table creation code.It's probably a good thing I have a fair sense of paranoia. I simply had the feeling that there must be something fishy with the question. I first focused in the wrong direction (suspecting some weird behaviour of DATALENGTH with the text datatype), but then luckily remembered the issue with text and triggers.
Me too. I wasted time making sure that that trigger really would mean that 3 rows would be returned if the trigger did what it looked as if it would do before deciding no that was too simple and looking harder at whether the definition was valid. But I'm not realy paranoid, except about statements made by journalists, politicians, economists, bankers and of course QotD.
After this one I'm sorely tempted to produce a really nasty qwuestion involving nested triggers - but perhaps that would be a step too far down the "you may spot the catch if you are lucky" path.
But of course I quite like questions like this - it isn't really a trick question at all, just one with a nice distractor.
Tom
April 9, 2013 at 8:56 pm
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 10, 2013 at 7:13 am
Danny Ocean (4/9/2013)
Hugo Kornelis (4/9/2013)
Danny Ocean (4/9/2013)
BUT statement 6 (ID 4 & 5) execute successfully and inserted two rows in table. In this we are using UNION ALL.How it's possible ? Anyone let me know..
Take a close look at the actual code in the trigger. It contains a COUNT(*), so it counts the number of rows that satisfy a given condition. In the case of statement 6, both of the inserted rows will qualify, so the COUNT(*) is equal to 2. And the trigger only raises and error and rolls back the transaction if that number is 1.
Thanks Hugo 🙂
+1... me too watch the question blindly.... tanks hugo....
Manik
You cannot get to the top by sitting on your bottom.
April 11, 2013 at 2:02 pm
Good and Timely Question,
Even though the text type is deprecated in 2008 and later versions, this just came up last week when we were asked to create a "archive/log" table and triggers on a table, which had a text column in it. The database supports an older Accounting package.
We were able to get around this problem in the the update (only new values) and insert triggers by joining the INSERTED table key value to the source table key value. Since the data is already gone in the DELETED tables by the time our triggers fired, we were a bit out of luck on the DELETE and old values of the update trigger.
As usual, I always learn a lot from the QotD. Thanks everyone.
Anton
April 26, 2013 at 3:41 am
I'm glad that fortunately I checked table's definition and while going through trigger definition got the clue. Than it was easy for me to pick right answer. 🙂
February 26, 2015 at 3:36 am
I Looked at msdn before giving answer, so got it right . Nice question
June 9, 2015 at 12:20 am
I have had the answer, without seeing that the trigger wasn't created. 😀
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply