You are the database administrator for your company and manage all the SQL Server 2008
databases of the company. You are responsible for performing the daily backup activities on
all the databases on the server. Database users regularly create new objects in one of the
databases. You want to be notified when new objects are created in the database.
You want to create a trigger that will fire whenever a new user-defined function is created in
the database. The trigger should insert statement-specific data, such as the name of the
database user who issued the CREATE FUNCTION statement, the time the statement was
issued, and the Transact-SQL statement that was issued, into a database table named
Log_structures. The Log_structures table was created by using the following statement:
CREATE TABLE Log_structures ( user1 nvarchar(100), createtime datetime, SQL_stat nvarchar(2000) );
Which statement should you use to create the trigger?
A.
CREATE TRIGGER Audit_functions
ON DATABASE AFTER CREATE_FUNCTION AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES( CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’));
B.
CREATE TRIGGER Audit_functions
AFTER CREATE_FUNCTION ON DATABASE AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT Log_structures(USER1, CREATETIME, SQL_stat)
VALUES( CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’));
C.
CREATE TRIGGER Audit_functions
ON DATABASE AFTER CREATE_FUNCTION AS
SET @mydata = EVENTDATA()
INSERT INTO Log_structures(USER1, CREATETIME, SQL_stat)
VALUES( CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’));
D.
CREATE TRIGGER Audit_functions
ON DATABASE AFTER CREATE_FUNCTION AS
DECLARE @mydata XML
SET @mydata = EVENTDATA()
INSERT Log_structures(USER1, CREATETIME, SQL_stat)
VALUES( CONVERT(nvarchar(100), CURRENT_USER), GETDATE(),
@mydata.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(2000)’));