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 a table before and after a data modification and take action 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
Explanation:
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.
https://msdn.microsoft.com/en-us/library/ms179562.aspx
DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. However, FOREIGN KEY constraints are usually used for this purpose.
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.
Answer D.
26
7
A trigger is a special type of stored procedure that automatically runs when a language event executes. Because of the Microsoft SQL Server integration with the .NET Framework common language runtime (CLR), you can use any .NET Framework language to create CLR triggers.
answer: B
7
1
Trigger is definitely a procedure but procedure is not definitely a trigger. First two requirements should already make it obvious that you should use a trigger.
From: https://docs.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-2017
“In DML triggers, the inserted and deleted tables are primarily used to perform the following:
-Extend referential integrity between tables.
-Insert or update data in base tables underlying a view.
-Test for errors and take action based on the error.
-Find the difference between the state of a table before and after a data modification and take actions based on that difference.”
5
0