You are a database administrator for AIOTestKing.com. The company hosts SQL Server 2005 databases for subscription-based customers. New customer databases are created frequently. Databases are removed when customers subscriptions end. Company managers want to track who creates each database and when each database is created. To store this information, you create a SQL Server database named Logging. You need to ensure that the appropriate information is written to the Logging database. What should you do?
A.
Use the SQL Trace stored procedures to log database creation activity.
B.
Create a stored procedure. Configure the stored procedure to write the appropriate information to the Logging database.
C.
Create a DDL trigger that runs when a new customer database is created. Configure the DDL trigger to write the appropriate information to the Logging database.
D.
Configure Service Broker to run a stored procedure that writes the appropriate information to the Logging database.
Explanation:
DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger depends on the event. For example, a DDL trigger created to fire in response to a CREATE TABLE event will do so whenever a CREATE TABLE event occurs in the database.
A DDL trigger created to fire in response to a CREATE LOGIN event will do so whenever a CREATE LOGIN event occurs in the server. You can use the CREATE_DATABASE DDL statement to create a trigger that fires when a database is created.