MCSA SQL Server 2016 ／70-762／似た者同士・TRIGGER＆Procedure

【TRIGGERとProcedure】

１．

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

Correct Answer: D C

Triggerはreturnできません！
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

---

2.

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 TRY"¦CATCH error handling
- Be written by using Transact-SQL statements.
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

Correct Answer: C

Triggerはreturnできません！

---

3.

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

Correct 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

---

4.

You are developing and application to track customer sales.
You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.
You need to create a database object.
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

Correct Answer: E
User-defined scalar functions return a single data value of the type defined in the RETURNS clause.
References:
https://technet.microsoft.com/en-us/library/ms177499(v=sql.105).aspx

---

5.

You are developing and application to track customer sales.
You need to create an object that meet the following requirements:
- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
- Run within a transaction and roll back if a future occurs.
- Run when a table is created or modified.
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

Correct Answer: B
The common language runtime (CLR) is the heart of the Microsoft .NET Framework andprovides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

---

6.

You are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements:

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

Correct Answer: C
References:
https://msdn.microsoft.com/en-us/library/ms345075.aspx

---

 7.

...いろいろと関係なさそうなのは前後割愛してます。

You must modify the Orders table to meet the following requirements:
✑ Create new rows in the table without granting INSERT permissions to the table.
✑ Notify the sales person who places an order whether or not the order was completed.

You need to modify the environment to meet the requirements for the Orders table.
What should you create?

A. an AFTER UPDATE trigger

B. a user-defined function

C. a stored procedure with output parameters

D. an INSTEAD OF INSERT trigger

Correct Answer: D
From Question: You must modify the Orders table to meet the following requirements:
✑ Create new rows in the table without granting INSERT permissions to the table.
✑ Notify the sales person who places an order whether or not the order was completed.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

---

8.

You have a view that includes an aggregate.
You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.
You need to ensure that you can update the view.
What should you create?

A. a DML trigger

B. a schema-bound view

C. a stored procedure

D. a DDL trigger

Correct Answer: A B

もしAggregate in Function-集計が存在するファンクションをつかったVIEWがUpdatableViewではないVIEWだったら、DMLトリガー（Instead OF Trigger）で変更するしかない。UpdatableViewはこちらで確認　

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

よって、『You have a view that includes an aggregate.』の部分で、『an aggregate』がUDFのファンクションにあれば、『A. a DML trigger』が正しい。なければ、UPDATABLE VIEWなので、『B. a schema-bound view』が正しい。

When you create a view, you must obey the following conditions in order to make the view updateable:
✑ The columns being modified in the view have to directly reference the underlying table columns. As a consequence, the view's columns being modified cannot be the result of an aggregate function or a computed column.
The only way to make data changes on a non-updateable view is by using INSTEAD OF DML triggers. This way you can use procedural code to overcome the limitation.
References:
https://www.mssqltips.com/sqlservertip/5984/sql-server-trigger-on-view-example/

---