February 15, 2012 at 8:07 am
Learnt something new, though I answered wrongly...
February 15, 2012 at 8:08 am
Hugo Kornelis (2/15/2012)
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!
And learnt even more...
February 15, 2012 at 8:14 am
SathishK (2/15/2012)
Hugo Kornelis (2/15/2012)
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!
And learnt even more...
Go back and look at the QOD - Steve added the ;
February 15, 2012 at 8:56 am
Good question!!
Thanks
February 15, 2012 at 9:17 am
great question and good lesson for me on an area i am less familiar with - tks
February 15, 2012 at 9:47 am
Sean Lange (2/15/2012)
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.
One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.
Another good example for instead of triggers (but instead of delete, not instead of insert) would be to transparently implement soft deletion.
February 15, 2012 at 9:48 am
bitbucket-25253 (2/15/2012)
SathishK (2/15/2012)
Hugo Kornelis (2/15/2012)
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!
And learnt even more...
Go back and look at the QOD - Steve added the ;
And removed the column list from the alias. Good job, Steve! 😉
February 15, 2012 at 10:06 am
Hugo Kornelis (2/15/2012)
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!
+1
This script will not run because it will not get compiled.
February 15, 2012 at 11:46 am
Darn. Second guessed myself and got it wrong. I don't use triggers often but I have been using merge more and more recently. Good to know anyways.
February 15, 2012 at 11:52 am
Hugo Kornelis (2/15/2012)
Sean Lange (2/15/2012)
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.One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.
Another good example for instead of triggers (but instead of delete, not instead of insert) would be to transparently implement soft deletion.
Thanks Hugo. I had not thought of the insert that way.
As for soft deletes, I like the example but I avoid soft deletes like the plague. 😛
_______________________________________________________________
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/
February 15, 2012 at 12:59 pm
Good question (I was lucky and didn't see it until after the errors were fixed ;-).
I didn't know the answer (I'm very familiar with instead of triggers but I'm not really familiar with merge) so I looked at the BoL page for merge and asked my browser to find "trigger" on that page - and that took me straight to it. So I learned something :-), but it was so easy to find that I may well forget it :(.
Tom
February 15, 2012 at 1:35 pm
Hugo Kornelis (2/15/2012)
Sean Lange (2/15/2012)
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.One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.
An example of an instead of insert trigger on a table (as opposed to on a view) is a trigger that ensures that some uniqueness constraint is met by inserting only rows that won't cause it to be violated and discarding other rows - if we want to insert those rows and not let the duplicates (if any) make teh constrain cause the insert to fail, an instead of insert trigger is the only way to do it. This works nicely when recursive triggers are disabled. Maybe also when they are enabled.
Of course for INSERT statements, a stored procedure containing much the same code as the trigger could be produced, and everything that wants to insert has to call thet proc instead - but I think this SP approach may not always be possible if MERGE statements are being used.
Tom
February 15, 2012 at 2:49 pm
I didn't know about this "feature." It's good to know.
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
February 15, 2012 at 3:19 pm
L' Eomot Inversé (2/15/2012)
Hugo Kornelis (2/15/2012)
Sean Lange (2/15/2012)
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.One point where I have used them is for inserting, updating and deleting with a view as a target, where the definition of the view turned out to be too complex for the automamted view-to-table translation that SQL Server does.
An example of an instead of insert trigger on a table (as opposed to on a view) is a trigger that ensures that some uniqueness constraint is met by inserting only rows that won't cause it to be violated and discarding other rows - if we want to insert those rows and not let the duplicates (if any) make teh constrain cause the insert to fail, an instead of insert trigger is the only way to do it. This works nicely when recursive triggers are disabled. Maybe also when they are enabled.
Of course for INSERT statements, a stored procedure containing much the same code as the trigger could be produced, and everything that wants to insert has to call thet proc instead - but I think this SP approach may not always be possible if MERGE statements are being used.
Thanks Tom. I suppose that would let you stick those offending records in an error table or something too.
_______________________________________________________________
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/
February 15, 2012 at 3:37 pm
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply