Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
In everyday life, not everything you plan on doing goes your way. For example, recently I planned to turn left on Rosewood Avenue to head north to my office. To my surprise, the road was blocked because of construction. I still needed to head north, even though the signs told me that turning that direction was impossible. I could have treated the unexpected roadblock in the same way that SQL Server interprets a level 16 severity error. That is to say, I could have halted and simply abandoned my attempt to travel to the office. In the end, I came up with an alternate plan that was nearly as good. By using a detour route I was successfully able to avoid this unexpected disruption until the construction zone was cleared. When my “Plan-A” route didn’t work, I tried and found a workable “Plan-B”. Structured Error Handling in SQL Server is similar to the way we approach ‘errors’ in real life. When something does not go exactly as we expected, we adapt and find another way to accomplish our goal.
Structured Error Handling
SQL Server introduced new and improved options for error handling beginning with SQL Server 2005. Prior versions did not include structured error handling.
Anticipating Potential Errors
We’ll begin with an example from the Grant table. We’ll attempt to set a GrantName value to NULL. We expect to raise an error, since GrantName is a required field. The GrantName for Grant 001 fails to update to a value of NULL, since that field does not allow nulls.
We can anticipate that updates might fail with an error. We can’t prevent the occasional incorrect input from the client, but best practices require us to control or eliminate the error messaging and provide SQL Server instructions for how to behave when it receives incorrect input.
Let’s next add code to our current example in order to catch these types of input errors. Structured error handling will help head off errors raised by incorrect inputs received from the client. We expect our users will send correct values and data types into the Grant table most of the time. However, for those few instances where a user attempts an incorrect value, seeing a message from anything besides the application or having to restart the application will not be comfortable experiences for our users. We will add code to deal with these types of errors and ensure that our users receive a message from the application layer (preferably one which gives them a hint they’ve attempted an incorrect value and providing guidance as to what the correct input should be).
The TRY Block
The Try Block is where you place code which you think may raise an error. A Try Block is a code segment starting with a BEGIN TRY statement and ending with END TRY. If a statement inside theTry Block and raises an error, then the error gets passed to another part of SQL Server and not to the calling code. The Try Block is aware that there is code which may fail.
The code which is vulnerable to potentially receiving bad input from the client should be enclosed within the Try Block. Think of your TRY block as Plan-A. This is what you are hoping works on the first try. If Plan-A does not work, we can tell SQL Server to try Plan-B instead of reacting with an error message. Our code which will handle contingency steps (i.e., Plan-B) for bad input will be enclosed in what’s known as a Catch Block. Only one of these two blocks will run to completion. Think of the Try Block as “Plan-A” and Catch Block as “Plan-B.”
The CATCH Block
The “Catch Block” serves as a contingency plan for failed code from the Try Block. In other words, if any statement raises a level 11 or higher severity in the Try Block, it will not show the error from the calling code. It will run the code you have set up in the Catch Block.
In the code example below the “Catch Block” never runs, since the Try Block (“Plan-A”) runs fine. In other words, if Plan-A works then there is no need to try Plan-B.
BEGIN TRY
UPDATE [Grant] SET GrantName = '92 Per-cents %% team'
WHERE GrantID = '001'
END TRY
BEGIN CATCH
PRINT 'No Change was made'
END CATCH
Notice the Catch Block never runs, since the Try Block (“Plan-A”) ran OK. Next let’s observe an example where the Try Block will throw an error and not run successfully. Recall our previous example where attempting to set GrantName to NULL threw an error. In using this same update statement, the Try Block encounters an error and thus will not run. The Catch Block will run instead.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter10.0Setup.sql script from Volume 4.
Question 28
You have tables named CurrentProducts and SalesInvoiceHeader. The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. You are deleting ProductID 77 from the Product table and then trying to insert a sale for Product77 into the SalesInvoiceHeader table.
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CurrentProducts WHERE ProductID = 77
BEGIN TRANSACTION
INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 )
COMMIT TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH
What will be the outcome when you run this query?
- 1 The product will be deleted from the CurrentProducts table.
2) The order details will be inserted into the SalesInvoiceHeader table. - 1) The product will be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table. - 1) The product will not be deleted from the CurrentProducts table.
2) The order details will be inserted into the SalesInvoiceHeader table. - 1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
47 Comments. Leave new
The correct answer is #4
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
This is a very interesting and a bit complex question and in addition to knowing TRY/CATCH we need to know the behavior of the nested transactions. When we rollback nested transactions all transactions are rolled back. I knew it, but just in case I experimented with this code to be absolutely sure in my answer.
Thanks for this interesting article and great question
I am from USA
Correct answer is No. 2: (a) The product will be deleted from the CurrentProducts table, and (b) The order details will not be inserted into the SalesInvoiceHeader table. This is because both tables have a foreign key relationship. We can delete a record from CurrentProducts table, but after deletion we can’t insert that deleted record into SalesInvoiceHeader, where products are referred by using a foreign key, which is precisely the one we just have deleted.
Rene Castro
El Salvador
Answer : Option 4
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Explanation : The product table having a FK relationship with SalesInvoiceHeader table.
It means SalesInvoiceHeader –> ProductId is a PK.
In first transaction, it will delete the row from product table.
In second transaction, When we are trying to insert dups in SalesInvoiceHeader, it will blow up and come to the catch block and rollback the deleted record from Product table.
USA
Ans:
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Lalit
India
Option 4) is correct
4) 1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
After the product is deleted from CurrentProducts the entry order details in salesinvoiceheader will throw and error as this cannot be inserted. Because of the thrown error the products deleted will go through Rollback of its transaction. hence Product will also not be deleted.
Leo Pius
USA
Question 28
Ans : option —-#4
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Chennai, INDIA
Correct answer is option #4.
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Explanation:
Since ProductId has FK relationship with SalesInvoiceHeader before deletion of record in CurrentProducts table we need to delete it in SalesInvoiceHeader.
So option 4.(1) is correct.
Because here exception will come on line no 3 in T-SQL code
DELETE FROM CurrentProducts WHERE ProductID = 77
So control will directly move to CATCH block,no chance of insertion in SalesInvoiceHeader table.
So option 4.(2) is correct.
New Delhi
India
Option 4) is correct
Manoj
India
The correct answer is option 4
Thanks,
prasad yangamuni
INDIA (PUNE)
Correct Answer is # 4.
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
4 ko lock kiya jaye
Manas
India
Option 4 is the Correct Answer
Thanks,
Narendra(India)
Hi Sir,
The correct option is 4th option
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Step 1 : It will delete the ProductID = 77 from the CurrentProducts table.
step 2 : While tryin to insert the order with ProductId 77 in the SalesInvoiceHeader table the statement will fail as there is foreign key relationship with CurrentProducts table.
Step 3 : Then the catch block will execute which rolls back the transaction hence the deletion of ProductID = 77 will be rolled back.
Therefore the Product will not be deleted from the CurrentProducts table and the order details will not be inserted into the salesInvoiceHeader table.
Nice question sir.
Thanks for the knowledge.
P.Anish Shenoy,
INDIA, Bangalore, Karnataka.
Correct Answer : Option 4
4) 1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
Chennai, TamilNadu, India
Correct option is 4.
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. The product will not be deleted because of the foreign key constraint and since it is not deleted the insert statement will not take place.
(Sale, Nigeria)
The correct answer is: #4.
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
The reason is quite simple, actually and involves knowledge of two concepts: TRY…CATCH (obviously) and “nested” transactions.
There are no nested transactions without SAVEPOINT, and therefore the ROLLBACK rolls back all open transactions all the way to the outermost transaction.
Country of residence: India
ans 4.
4. 1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
first it will delete the product from the product table .Then it will try to insert the value in the SalesInvoiceHeader table at that time one sql server throw an error so it will go to catch block and though no transaction committed till so it will roll back transaction .So Delete operation will rollback .
So the result will be neither delete from currentProducts nor insert in the SalesInvoiceHeader .
so only option 4 is correct.
Partha,
India
Correct answer option no. 4
explanation : first delete query throw an exception because of fk relation between two tables.
and transaction rollback.
shekhar gurav.
country : India
The answer is #4
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
The product will not be deleted from the CurrentProducts table because there is a Foreign Key Constraint between it and the SalesInvoiceHeader table. The record would need to be deleted from the SalesInvoiceHeader table first and then from the CurrentProducts table. SInce it is not deleted from SalesInvoiceHeader first, the statement will fail and move into the CATCH block. Because it moved into the catch block right away, the Insert code is never called and thus the order details do not get inserted into SalesInvoiceHeader.
Deb – USA
The correct answer for the above question is Option #4)
1) The product will not be deleted from the CurrentProducts table.
2) The order details will not be inserted into the SalesInvoiceHeader table.
dilip kumar jena
country india