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)’));
Explanation:
You should use the following statement to create the trigger:
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)’));This statement uses the correct syntax to create a trigger that will fire whenever a new function is created in the database. The trigger will insert the name of the current user, the time, and the Transact-SQL statement that caused the trigger to fire into the Log_structures table. The CREATE_FUNCTION event is fired when a function is created in the database. The ON DATABASE clause in the statement specifies that the scope of the trigger is the current database. Therefore, this trigger will be fired whenever a function is created in the current database. The trigger body also uses the EVENTDATA function. This function returns information regarding the database event that caused the trigger to fire, such as the name of the instance on which the event was fired, the Transact-SQL statement that caused the event to fire, and the type of event that was fired. This function can be called from within trigger to return the specified information. The following line in the CREATE TRIGGER statement specifies that the Transact-SQL statement that caused the trigger to be fired should be returned: @mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’)) The value returned is stored in the Log_structures table. The Log_structures table also stores the name of the database user who created the function and the time at which the function was created. The CURRENT_USER function returns the name of the user who executed the statement, and the GETDATE() function returns the date and time the statement was executed. You should not use the following statement:
CREATE TRIGGER Audit_functions AFTER CREATE_FUNCTION ON DATABASE 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)’));This statement is syntactically incorrect. In a CREATE TRIGGER statement, the ON DATABASE clause appears before the AFTER event_name clause. To correct this statement, you should place the ON DATABASE clause before the AFTER CREATE_FUNCTION clause. You should not use the following statement:
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)’));The mydata variable is not declared. You cannot use the variable until you declare the variable by using the DECLARE statement. You should not use the following statement: 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)’))
This statement inserts the EventType property returned by the EVENTDATA function. In this scenario, you are required to insert the Transact-SQL statement into the Log_structures table. To insert the Transact-SQL statement into the Log_structures table, you should replace @mydata.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(2000)’)) with the @mydata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’)) .
Objective: Managing SQL Server Security Sub-Objective: Audit SQL Server instances.
References: TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > CREATE TRIGGER (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > EVENTDATA (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > DDL Triggers > Designing DDL Triggers > DDL Events MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > DDL Triggers > Designing DDL Triggers > Using the EVENTDATA Function