none
Server level DDL triggers

    Question

  • Hi all,

    Below I have a trigger that fires whenever a ddl statement is issued and tracks it in a table called tblMonitorChange.

    USE AdventureWorks
    GO 
    CREATE TRIGGER trgMonitorChange1
    ON Database
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    set nocount on
    declare @EventType varchar(100)
    declare @SchemaName varchar(100)
    declare @ObjectName varchar(100)
    declare @ObjectType varchar(100)
    SELECT 
     @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') 
    ,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') 
    ,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
    ,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')  
    -- Is the default schema used 
    if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p 
                on u.uid = p.principal_id where u.name = CURRENT_USER
    insert into tblMonitorChange 
       select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN() 
    
    
    
    USE AdventureWorks
    GO 
    CREATE TABLE [dbo].[tblMonitorChange](
     [EventType] [varchar](100) NULL,
     [SchemaName] [varchar](100) NULL,
     [ObjectName] [varchar](100) NULL,
     [ObjectType] [varchar](100) NULL,
     [EventDate] [datetime] NULL,
     [SystemUser] [varchar](100) NULL,
     [CurrentUser] [varchar](100) NULL,
     [OriginalUser] [varchar](100) NULL)
    
    


    Is it possible to have this on the server or instance level instead of the database level and track the database and the column namein the monitor table  in case of an Alter table add column statement...?

    Thx for any hint..

    L.

    Monday, July 25, 2011 8:55 AM

Answers

All replies

  • Hi Ludwig

    For creating the trigger on server level

    use the ON ALL SERVER instead of ON DATABASE after the create trigger statement.

    I hope you can work out the rest , by yourself.

     

    Surendra

     

     


    Nothing is Permanent... even Knowledge....
    • Proposed as answer by Vishal Gajjar Monday, July 25, 2011 9:42 AM
    • Marked as answer by Ludwig AR Monday, July 25, 2011 7:43 PM
    Monday, July 25, 2011 8:59 AM
  • Hi,

    you can change your code 

    CREATE TRIGGER trgMonitorChange1
    ON ALL SERVER FOR create_table
    as
    begin
    set nocount on
    declare @EventType varchar(100)
    declare @SchemaName varchar(100)
    declare @ObjectName varchar(100)
    declare @ObjectType varchar(100)
    SELECT 
     @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') 
    ,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') 
    ,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
    ,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') 
    -- Is the default schema used 
    if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p 
          on u.uid = p.principal_id where u.name = CURRENT_USER
    print 'a table created'
    end
    


    • Proposed as answer by Vishal Gajjar Monday, July 25, 2011 9:42 AM
    Monday, July 25, 2011 9:01 AM
  • you will also need a new column for DATABASE name, which can be retrieved from EVENTDATA() as 

    EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')

     

    http://sqlandme.com/2011/07/15/sql-server-auditing-schema-changes-using-ddl-triggers/ 


    - Vishal

    SqlAndMe.com

    • Proposed as answer by ismailadar Monday, July 25, 2011 11:03 AM
    • Marked as answer by Ludwig AR Monday, July 25, 2011 7:43 PM
    Monday, July 25, 2011 9:58 AM
  • you will also need a new column for DATABASE name, which can be retrieved from EVENTDATA() as 

    EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')

    http://sqlandme.com/2011/07/15/sql-server-auditing-schema-changes-using-ddl-triggers/ 


    - Vishal

    SqlAndMe.com

    Thanks for the valuable information.

    One quice question please:

    Where the triggers are actually cteated? In which database,

    If we want to drop the triggers and rollback the total DDL trigger modification, what is the next activity.


    DBA

    Tuesday, November 13, 2012 6:59 AM
  • Thanks for the valuable information.

    Please guide me the rollback plan for the same.

    I have created the triggers in testing environment.

    From which database to drop the triggers?


    DBA

    Tuesday, November 13, 2012 7:48 AM