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 43 discussion

Actual exam question from Microsoft's 70-762
Question #: 43
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:
✑ Launch when table data is modified.
✑ Evaluate the state of a table before and after a data modification and take actions based on the difference.
✑ Prevent malicious or incorrect table data operations.
✑ Prevent changes that violate referential integrity by cancelling the attempted data modification.
✑ Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?

  • A. extended procedure
  • B. CLR procedure
  • C. user-defined procedure
  • D. DML trigger
  • E. scalar-valued function
  • F. table-valued function
  • G. DDL trigger
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
You can create a database object inside SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime
(CLR). Database objects that can leverage the rich programmingmodel provided by the CLR include DML triggers, DDL triggers, stored procedures, functions, aggregate functions, and types.
Creating a CLR trigger (DML or DDL) in SQL Server involves the following steps:
Define the trigger as a class in a .NETFramework-supported language. For more information about how to program triggers in the CLR, see CLR Triggers. Then, compile the class to build an assembly in the .NET Framework using the appropriate language compiler.
Register the assembly in SQL Server using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies
(Database Engine).
Create the trigger that references the registered assembly.
References:
https://msdn.microsoft.com/en-us/library/ms179562.aspx
by rrajnoha at July 26, 2019, 11:08 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
rrajnoha
Highly Voted 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 33 times
gripasha
5 years, 2 months ago
Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server. Why are you not paying attention to this requirement?
upvoted 13 times
rya
5 years, 2 months ago
i agree with gripasha, the answer is B
upvoted 5 times
JohnFan
5 years, 2 months ago
It is surprised to see that rrajnoha got upvoted 9 times
upvoted 1 times
JohnFan
5 years, 1 month ago
So may be rrajnoha is right.
upvoted 3 times
NickMane
4 years, 7 months ago
JohnFan still irrelevant and trolling
upvoted 3 times
...
Load full discussion...
...
...
...
Anette
4 years, 10 months ago
You can create TRIGGER objects in managed code.
upvoted 4 times
stm22
4 years, 9 months ago
agree: https://docs.microsoft.com/en-us/sql/relational-databases/triggers/create-clr-triggers?view=sql-server-ver15
upvoted 1 times
...
...
kiri2020
4 years, 6 months ago
the first condition says - "Lunch when table data is modified" - must be trigger
upvoted 2 times
...
...
Anette
4 years, 10 months ago
I Agree
upvoted 1 times
...
NickMane
4 years, 7 months ago
A trigger is a special type of stored procedure, so B still correct
upvoted 1 times
Hoglet
4 years, 4 months ago
No, a DML is a trigger and an option. Stored Procedure is not the right answer
upvoted 1 times
...
...
...
JohnFan
Highly Voted 5 years, 1 month ago
A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also 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.
upvoted 10 times
...
Alex5x
Most Recent 4 years, 5 months ago
The following 4 requirements clearly point to DML trigger: ✑ Launch when table data is modified. ✑ Evaluate the state of a table before and after a data modification and take actions based on the difference. ✑ Prevent malicious or incorrect table data operations. ✑ Prevent changes that violate referential integrity by cancelling the attempted data modification. The only confusing requirement is this one: ✑ Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
upvoted 3 times
Alex5x
4 years, 5 months ago
What does this requirement mean? It means the following: 1) You have already created .net stored procedure, e.g.: public class HelloWorldProc { [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld(out string text) { SqlContext.Pipe.Send("Hello world!" + Environment.NewLine); text = "Hello world!"; } } 2) You have compiled it into an assembly, e.g.: csc /target:library helloworld.cs 3) And you have already created the assembly, e.g.: CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE
upvoted 1 times
...
Alex5x
4 years, 5 months ago
So, the only question we need to answer is whether we can call that CLR SP from our trigger. The answer is yes, we can do this: Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement: CREATE PROCEDURE USP_hello @i nchar(25) OUTPUT AS EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld Now we can call the USP_hello from our trigger: DECLARE @J nchar(25) EXEC USP_hello @J out; So the answer is D. DML trigger.
upvoted 4 times
MarcusJB
4 years, 5 months ago
Exactly! Because in the task description it doesn't say "logic that is written by using managed code" like in task #41.
upvoted 2 times
...
Hoglet
4 years, 4 months ago
While you are correct that it's D, you don't need to call a CLR Stored Procedure from the trigger. You can write the trigger in a managed language like C#. It would be a CLR DML Trigger
upvoted 2 times
...
...
...
strikersree
4 years, 10 months ago
Correct answer is DML trigger, which is a CLR Trigger. Here is the explanation from Microsoft Documentation: A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also 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.
upvoted 5 times
strikersree
4 years, 10 months ago
If there is a CLR Trigger option in the exam, I would go with that.
upvoted 3 times
...
...
raja1234567890
4 years, 11 months ago
DDL Trigger is right answer as managed code is already registered as assembly, trigger can execute stored procedure that created on top of assembly
upvoted 1 times
Hoglet
4 years, 4 months ago
DDL is "Data Definition Language" We want this when the data is modified, ie. DML Trigger
upvoted 1 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