Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 41 discussion

Actual exam question from Microsoft's 70-762

Question #: 41
Topic #: 1

Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
- Return a value of 0 if data inserted successfully into the Customers table.
- Return a value of 1 if data is not inserted successfully into the Customers table.
- Support logic that is written by using managed code.
What should you create?

A. extended procedure
B. CLR procedure
C. user-defined procedure
D. DML trigger
E. DDL trigger
F. scalar-valued function
G. table-valued function

Show Suggested Answer

Suggested Answer: B 🗳️
DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements.DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger canalso be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.
References:
https://msdn.microsoft.com/en-us/library/ms178110.aspx

by Ben47 at July 6, 2019, 2:01 a.m.

Comments

Submit Cancel

ThatGuy

Highly Voted 5 years, 10 months ago

I agree that a trigger can be written in managed code. HOWEVER it cannot do a return. Therefor I think that this should be a procedure.

upvoted 21 times

Nelly100

5 years, 2 months ago

I wonder where you got this theory that triggers dont return results? Returning Results The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that aren't designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do. To prevent triggers from returning result sets, set the disallow results from triggers option to 1. The correct answer is C i.e. DML Trigger

upvoted 2 times

Hiken90

4 years, 11 months ago

A trigger can't return a 1 or 0 value, i wonder where you got the theory that it can

upvoted 2 times

...

Hoglet

4 years, 6 months ago

Yes, you can return any number of result set that you like during the execution of a trigger. But how are you going to consume that result in your code? It works if you are running it interactively in SSMS, but try using it PowerShell, ADO.Net or EF and you can't process those result sets.

upvoted 1 times

...

JohnFan

Highly Voted 5 years, 3 months ago

CLR (Common Language Runtime) These are procedures that are written in a .NET language and have a Transact-SQL calling mechanism. It is important to understand what the CLR objects are. Using managed, .NET code you can create STORED PROCEDURE objects that behave and are called exactly like ones created in interpreted SQL. The .NET code used has access to everything that the interpreted code does as well. These objects are not created using Management Studio in Transact-SQL, but are built in Visual Studio like other .NET programs.

upvoted 9 times

...

SoupDJ

Most Recent 4 years, 8 months ago

2 things to think about: (1) triggers operate in the same transaction as the statement that calls them. If the triggering action fails, then both are rolled back - so I don't see how a trigger could provide a return value if the triggering action statement failed

upvoted 1 times

...

Solis

4 years, 10 months ago

Answer is B for it says "Support logic that is written by using managed code." and return an integer value which a CLR procedure does.

upvoted 7 times

...

Anette

5 years ago

I think its Scalar value function, F

upvoted 1 times

Anette

5 years ago

OR Procedure, but which A,B or C? I am not sure

upvoted 1 times

Anette

5 years ago

Ok, Now I am confused, because managed code is used fro triggers, as stated in Study Guide. But anyway, triggers cannot return a value. So triggers are about to fail in this question. But I found in Microsoft official documents stated that "Code that runs within the CLR is referred to as managed code" so the most possible way is that the correct answer is B. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration#:~:text=NET%20Framework%20code.,referred%20to%20as%20managed%20code.&text=With%20the%20CLR%20hosted%20in,defined%20aggregates%20in%20managed%20code.

upvoted 4 times

...

Froze

5 years, 2 months ago

Based on 70-761 exam I've learn pattern, that every time question is about managed code you should think about CLR.

upvoted 9 times

...

NickMane

5 years, 3 months ago

triggers cannot return values, stored procedures can

upvoted 2 times

...

Dieter

5 years, 9 months ago

Agree with ThatGuy - we need to return values due to the results

upvoted 5 times

...

rrajnoha

5 years, 11 months ago

I think that DML trigger(Types of DML Triggers: AFTER trigger, INSTEAD OF trigger, CLR Triggers ) https://docs.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers?view=sql-server-2017

upvoted 1 times

...

Ben47

5 years, 11 months ago

Correct ans should be D : DML trigger DML can be used in managed code

upvoted 6 times

caeesz

4 years, 5 months ago

trigger cannot return

upvoted 1 times

...

Hoglet

4 years, 6 months ago

While a trigger can be managed code / CLR, how would you say that a trigger returned a value of 1 or 0 to demonstrate success or failure as per the requirement? It's a CLR Procedure

upvoted 2 times

...