Home » Microsoft » MB6-886 » Which data type should you use?
You are designing a database that contains a data definition language (DDL) trigger. The DDL trigger will provide the maximum amount of data available when any attempt is made to change the database schema.
You need to design a table to meet the following requirements:
• Accept the EVENTDATA information that is provided by the trigger
• Support the searching and retrieval of nodes and values
• Minimize development time
Which data type should you use?
A. nvarchar(max)
B. varchar(max)
C. varbinary
D. XML
Correct Answer: D
Explanation/Reference:
EVENTDATA( ) returns information about server or database events. EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger.EVENTDATA returns XML data.
The following example creates a DDL trigger to prevent new tables from being created in the database. The Transact-SQL statement that fires the trigger is captured by using XQuery against the XML data that is generated by EVENTDATA.
CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE
AS
PRINT ‘CREATE TABLE Issued.’
SELECT EVENTDATA().value
(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
RAISERROR (‘New tables cannot be created in this database.’, 16, 1)
ROLLBACK
;
GO
--Test the trigger.
CREATE TABLE NewTable (Column1 int);
GO
--Drop the trigger.
DROP TRIGGER safety
ON DATABASE;
GO
