Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 19 discussion

Actual exam question from Microsoft's 70-761

Question #: 19
Topic #: 1

DRAG DROP -
You have two tables named UserLogin and Employee respectively.
You need to create a Transact-SQL script that meets the following requirements:
✑ The script must update the value of the IsDeleted column for the UserLogin table to 1 if the value of the Id column for the UserLogin table is equal to1.
✑ The script must update the value of the IsDeleted column of the Employee table to 1 if the value of the Id column is equal to 1 for the Employee table when an update to the UserLogin table throws an error.
✑ The error message "No tables updated!" must be produced when an update to the Employee table throws an error.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
References:
https://msdn.microsoft.com/en-us/library/ms175976.aspx

by Houdinni at Aug. 6, 2019, 8:09 p.m.

Comments

Submit Cancel

Robintang0924

Highly Voted 5 years, 5 months ago

Correct(and complete) solution should look like below: BEGIN TRY UPDATE dbo.UserLogin SET IsDeleted = 1 WHERE Id=1 END TRY BEGIN CATCH BEGIN TRY UPDATE dbo.Employee SET IsDeleted=1 WHERE Id=1 END TRY BEGIN CATCH Raiserror ('No tables updated!', 16, 1) END CATCH END CATCH

upvoted 18 times

...

Houdinni

Highly Voted 5 years, 10 months ago

You should have the second TRY CATCH block nested inside the first CATCH block, in order to fulfill the condition: "The script must update the value of the IsDeleted column of the Employee table to 1 if the value of the Id column is equal to 1 for the Employee table when an update to the UserLogin table throws an error."

upvoted 13 times

...

Vermonster

Most Recent 4 years, 6 months ago

Agree that this answer from @Robintang0924 is correct using nested TRY/CATCH in CATCH block: BEGIN TRY UPDATE dbo.UserLogin SET IsDeleted = 1 WHERE Id=1 END TRY BEGIN CATCH BEGIN TRY UPDATE dbo.Employee SET IsDeleted=1 WHERE Id=1 END TRY BEGIN CATCH Raiserror ('No tables updated!', 16, 1) END CATCH END CATCH

upvoted 1 times

...

julie2020

4 years, 11 months ago

(1) BEGIN TRY UPDATE dbo.UserLogin SET IsDeleted = 1 WHERE Id=1 END TRY (2) BEGIN CATCH (3) BEGIN TRY UPDATE dbo.Employee SET IsDeleted=1 WHERE Id=1 END TRY (4) BEGIN CATCH Raiserror ('No tables updated!', 16, 1) END CATCH (5) END CATCH

upvoted 1 times

...

Anette

5 years, 1 month ago

The question is a little messy, there is no END TRY. And I found this which is the same question and seems to be ok: https://www.briefmenow.org/microsoft/which-five-transact-sql-segments-should-you-use-to-deve-6/ So, the corret answer must be: /*1*/ begin try update dbo.UserLogin Set IsDeleted = krugfogbeouegf Where Id = 1 end try /*2*/ begin catch /*3*/ begin try Update dbo.Employee Set IsDeleted = 1 Where Id = 1 end try /*4*/ begin catch RAISERROR('No tables updated!',16,1); end catch /*5*/ end catch

upvoted 7 times

ASQL

4 years, 8 months ago

agreed that is the correct solution

upvoted 2 times

...

daniel_yes23

5 years, 2 months ago

It says to throw an error only for Employee table. The bellow example is correct and tested. BEGIN TRY UPDATE dbo.UserLogin SET IsDeleted = 1 WHERE Id = 1 END TRY BEGIN CATCH END CATCH BEGIN TRY UPDATE dbo.Employee SET IsDeleted = 'a' WHERE Id = 1 END TRY BEGIN CATCH RAISERROR ('No table updated', 16, 1) END CATCH

upvoted 4 times

...

Guino

5 years, 3 months ago

-Begin try update duo.employee ... -Begin Catch -Update dbo.Employee set isdeleted=1 where Id=1 -Begin Catch Raiserror ('No tables update!', 16,1) end catch -End catch

upvoted 1 times

...

moehijawe

5 years, 5 months ago

begin try update dbo.userlogin ...... begin try update dbo.employee end try begin catch end catch begin catch raise end catch

upvoted 1 times

moehijawe

5 years, 5 months ago

sorry, updated: begin try update dbo.userlogin ...... begin catch begin try update dbo.employee end try begin catch raise end catch end catch

upvoted 3 times

...

Niv

5 years, 8 months ago

The right order is: 3 --> 5 --> 6 --> 1 --> You have to use nested Try...Catch

upvoted 1 times

...

HS_

5 years, 9 months ago

I dont understand as there is no END TRY option?

upvoted 9 times

tcroots19

5 years, 3 months ago

yeah, seems they just messed up the question

upvoted 4 times

Andy7622

4 years, 6 months ago

Yes, options are not correct . I saw the correct one on another site

upvoted 1 times

...

M4x

5 years, 9 months ago

Is not clear WHEN update the Employee table. WHEN the UserLogin throw an error OR ALWAYS: also when the userLogin update throw error

upvoted 1 times

...

Bartek

5 years, 10 months ago

Both condition are fine. You could put something like this answer eg. : BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH END CATCH BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH RAISERROR ('NO TABLES UPDATED',16,1) END CATCH And it II works fine Or You could do something like Houdinni says like eg : BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH RAISERROR ('NO TABLES UPDATED',16,1) END CATCH END CATCH

upvoted 7 times

RedHead

5 years, 9 months ago

BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH END CATCH BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH RAISERROR ('NO TABLES UPDATED',16,1) END CATCH above answer is wrong because if condition is true than it gives error. ; BEGIN TRY SELECT 1/1 END TRY BEGIN CATCH END CATCH BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH RAISERROR ('NO TABLES UPDATED',16,1) END CATCH below is ok BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH RAISERROR ('NO TABLES UPDATED',16,1) END CATCH END CATCH

upvoted 3 times

...