You work as a database developer for www.company.com.com. The company has a database named www.
company.com that is hosted on a SQL Server 2008 server. The database includes a table named Employees,
which contains the details of the employees. You want to ensure that anytime any data in the Employeestable
is modified with an UPDATE statement, they are recorded in a table named EmployeeChanges. The
EmployeeChanges table includes columns to record what the change was, who made the change, and when
they made the change. What should you create to meet this need?
A.
A DDL trigger that queries the updated table
B.
A DML trigger that queries the inserted table
C.
A DDL trigger that queries the inserted table
D.
A DML trigger that queries the updated table
Explanation:
A Data Manipulation Language (DML) trigger can be used to capture DML statements (such as UPDATE,
INSERT, and DELETE). DML triggers can
use the inserted and deleted tables, which are virtual tables that hold data from the last INSERT, UPDATE, or
DELETE statement.
An INSERT will record the inserted data in the inserted table. A DELETE will record the deleted data in the
deleted table. An UPDATE will record
the original row in the deleted table, and the modified row in the inserted table. The GetDate() function can be
used to retrieve the current date.
Answer D is incorrect. There is no such thing as anupdated table. The inserted and deleted tables areused to
record updated data.
Answer A and C are incorrect. DDL triggers will capture Data Definition Language statements (such as
CREATE, DROP, and ALTER) but will not capture statements that modify data.