ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 41 discussion

Actual exam question from Microsoft's 70-762
Question #: 41
Topic #: 1
[All 70-762 Questions]

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 Hide 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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
ThatGuy
Highly Voted 5 years, 8 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 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, 10 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, 4 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, 1 month 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, 6 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, 8 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
4 years, 10 months ago
I think its Scalar value function, F
upvoted 1 times
Anette
4 years, 10 months ago
OR Procedure, but which A,B or C? I am not sure
upvoted 1 times
Anette
4 years, 10 months 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, 1 month 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, 1 month ago
triggers cannot return values, stored procedures can
upvoted 2 times
...
Dieter
5 years, 7 months ago
Agree with ThatGuy - we need to return values due to the results
upvoted 5 times
...
rrajnoha
5 years, 9 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, 10 months ago
Correct ans should be D : DML trigger DML can be used in managed code
upvoted 6 times
caeesz
4 years, 3 months ago
trigger cannot return
upvoted 1 times
...
Hoglet
4 years, 4 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
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago