Friday, November 11, 2011

0 Notes about DDL Trigger

Get the trigger definition.

  1. SELECT st.name,definition
  2. FROM sys.sql_modules sm join sys.triggers st
  3. on sm.object_id = st.object_id
  • RAISEERROR() can write information to the event log.
  • PRINT can output the string to the console

Log the the trigger event to the table

Create table to hold the log information

  1. CREATE TABLE tDDLlog (PostTime datetime,LoginName nvarchar(100),
  2. DB_User nvarchar(100),Object sysname,
  3. Event nvarchar(100), TSQL nvarchar(2000),EventData XML);
  4. GO

Create the trigger to write to the table

  1. CREATE TRIGGER trlog
  2. ON DATABASE
  3. FOR DDL_DATABASE_LEVEL_EVENTS
  4. AS
  5. DECLARE @data XML
  6. SET @data = EVENTDATA()
  7. INSERT tDDLlog
  8.    (PostTime, LoginName, DB_User, Object, Event, TSQL, EventData)
  9.    VALUES
  10.    (GETDATE(),
  11.    CONVERT(nvarchar(100), @data.query('data(//LoginName)')),
  12.    CONVERT(nvarchar(100), CURRENT_USER),
  13.    CONVERT(sysname,@data.query('data(//ObjectName)')),
  14.    CONVERT(nvarchar(100), @data.query('data(//EventType)')),
  15.    CONVERT(nvarchar(2000), @data.query('data(//TSQLCommand)')),
  16.     @data) ;
  17. GO

Lets try some DDL statement ,then query the tDDLlog table

  1. CREATE TABLE TestTable (a int)
  2. ALTER TABLE TestTable Add b nvarchar(10)
  3. DROP TABLE TestTable ;

image

Lets expend the XML function

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates