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 Employees table 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 an updated table. The inserted and deleted tables
are used 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.