Which statement should you use to create the trigger?

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 objectsare 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, intoa database table named Log_structures . The Log_structures table was created by using the followingstatement:
CREATE TABLE Log_structures (
user1 nvarchar(100),
createtime datetime,
SQL_stat nvarchar(2000));
Which statement should you use to create the trigger?

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 objectsare 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, intoa database table named Log_structures . The Log_structures table was created by using the followingstatement:
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 thedatabase. The trigger will insert the name of the current user, the time, and the Transact-SQL statement thatcaused the trigger to fire into the Log_structures table. The CREATE_FUNCTION event is fired when a functionis created in the database. The ON DATABASE clause in the statement specifies that the scope of the trigger isthe 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 databaseevent 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 fromwithin trigger to return the specified information. The following line in the CREATE TRIGGER statement specifiesthat 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 thedatabase 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 dateand 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 appearsbefore the AFTER event_name clause. To correct this statement, you should place the ON DATABASE clausebefore 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 arerequired to insert the Transact-SQL statement into the Log_structures
table. To insert the Transact-SQLstatement 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 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > CREATE TRIGGER (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > EVENTDATA (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > DDLTriggers > Designing DDL Triggers > DDL Events MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > DDLTriggers > Designing DDL Triggers > Using the EVENTDATA Function

Show Hint

Leave a Reply 0

Your email address will not be published. Required fields are marked *