February 15, 2012 at 12:44 am
This was removed by the editor as SPAM
February 15, 2012 at 1:19 am
Nice question. Didn't know triggers had such an impact on MERGE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 1:37 am
Koen Verbeeck (2/15/2012)
Nice question. Didn't know triggers had such an impact on MERGE.
+1
I didn't really get the point you were making until I built the tables and the insert trigger, then everything became clear.
Thanks for highlighting this.
Ian
February 15, 2012 at 2:06 am
Bugger! Got bored Googling the answer (never done MERGE or INSTEAD OF Triggers before) and thought I'd found it so went for Yes. Only after being told I was wrong did I read the paragraph above the one I took my answer from...
http://technet.microsoft.com/en-us/library/bb510625.aspx
If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.
Boo hoo! ...as Cher would say... if I could turn back time! 😛
Great question though, I learned something new yet again. All good stuff.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
February 15, 2012 at 3:03 am
Koen Verbeeck (2/15/2012)
Nice question. Didn't know triggers had such an impact on MERGE.
+1
Good question!!!
thanks!
February 15, 2012 at 3:28 am
Great question! As with most people didn't think of all the limitations and was very annoyed when I got it wrong - with myself.
February 15, 2012 at 4:46 am
The BOL reference also says:
The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.
I didn't see that semicolon in the statement in the QOTD.
February 15, 2012 at 5:08 am
Good question! What a bizarre restriction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 15, 2012 at 5:11 am
cengland0 (2/15/2012)
The BOL reference also says:The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.
I didn't see that semicolon in the statement in the QOTD.
I got it right, for all the wrong reasons.
The script will not run, regardless of triggers, for two reasons:
1. The MERGE statements must be terminated with a semicolon. This one isn't, so the script would fail.
2. Adding column names to a table alias is not valid syntax. SQL Server will interpret this as a table hint - and neither Form_ID nor Form_Name is recognised as a valid hint.
Without those two errors, I would probably have given the wrong answer. Thanks for teaching me something I didn't know!
February 15, 2012 at 6:01 am
Suprised at the results
Incorrect answers: 66% (155)
Total attempts: 236
Thought many more would be correct, since BOL clearly states
If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.
February 15, 2012 at 7:00 am
Missed another point, but learned something today. Thanks for submitting.
http://brittcluff.blogspot.com/
February 15, 2012 at 7:10 am
bitbucket-25253 (2/15/2012)
Incorrect answers: 66% (155)
Total attempts: 236
Thought many more would be correct, since BOL clearly states
If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.
At 9:04 AM Eastern Standard Time
[Center]Correct answers: 33% (104)
Incorrect answers: 67% (208)
Total attempts: 312 [/Center]
February 15, 2012 at 7:17 am
cengland0 (2/15/2012)
The BOL reference also says:The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.
I didn't see that semicolon in the statement in the QOTD.
Indeed! This makes the answer "no" even more right! 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 7:38 am
Good question.
I don't think I have ever written an instead of trigger. I had to wrestle with them early in my career (didn't know how to disable a trigger at that point) and they were nothing but a pain. I still don't quite get the point to be honest. It is kind of like saying "you can insert data into this table but this trigger is going to do something different anyway". I can sort of conceptually see where they could be useful but it seems like such a rare occurrence.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply