Create TRIGGER on all databases 1

[glgcag1991]

I'm using code I got at a conference to generate a table to log all DDL events in my databases, then I have code to create a trigger on all my databases to add a record to the table upon any DDL event. Being the newbie that I am, I ran the trigger and it created in the first database on my server but none of the others. I'm assuming I need to loop through each database and add the trigger, but first, why does this code add it only to the first database in my server database list?

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS

I'm assuming it's the ON DATABASE part that is adding it to the first db in my list, but what exactly does ON DATABASE mean? If I knew the name of the db, wouldn't I just put ON [dbname], or at least a variable passed in if I didn't know it? (This is the newbie part of the question.)

Second, can I use sp_msforeachdb to cycle through the databases and fire off the create trigger? I'm not sure how to pass the create trigger code into the sp_msforeachdb proc- any suggestions?

I appreciate the help!

 

[hmckillop]

As you are already aware DDL triggers are used mainly as an administrative purpose either to restrict schema changes or to audit them.

"ON DATABASE" is an explicit statement used for DDL triggers and effectively implies that they are database wide rather than on specific tables. You cant change this option to be "My database"

If you need these on different databases use the "USE" statement first

e.g.

use MyDBName1
GO
CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS ....

USE MYDBName2
GO
CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 

....etc

In answer to second question "Second, can I use sp_msforeachdb to cycle through the databases and fire off the create trigger? I'm not sure how to pass the create trigger code into the sp_msforeachdb proc- any suggestions?
"

yes you can.

Try

Second, can I use sp_msforeachdb to cycle through the databases and fire off the create trigger? I'm not sure how to pass the create trigger code into the sp_msforeachdb proc- any suggestions?

You cant specify a schema name as a prefix to the trigger name for database or server level triggers.
You will need to use specific "USE DB" statements to run these.

Do you have many databases, could you not run these individually?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[glgcag1991]

Thanks for the explanation of ON DATABASE. It's the context of where the trigger is being added- at the database level, not the table, etc. level. That clears it up.

As far as adding the trigger to each database manually, yes, the environment I'm working in has 10-12 on each of the servers so it's definitely not a big deal, I'm just working on my post-install script tools and I thought it might be nice to fire off one proc that adds the trigger to each database.

Of course, if I'm doing the initial install of the server, I just need to add it to the model db and all new databases will get it automatically, but in the case that I'm jumping into an existing environment and I want to add my DDL Events logging, it would be nice to just run the proc.

I've been unable to loop using sp_msforeachdb and put my Create Trigger T-SQL in the @command1 parameter. I've tried creating a proc that creates the trigger but it won't work because the "USE" statement isn't allowed in a proc. Any other possible ways to make it work? Here's the complete Trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
        <> 'CREATE_STATISTICS'  
        INSERT  INTO ddl_changelog..DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
		  DatabaseName,
		  SchemaName,
		SystemUser,
		AppName,
		HostName,
                  tsql ,
		createddate
                ) 
        VALUES  ( 
                   @data.value('(/EVENT_INSTANCE/EventType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 
                              'nvarchar(100)'),
				 system_user , app_name (),host_name(),
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
                              'nvarchar(max)') ,
				getdate()
                ) ; 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE

Thanks for your help!

 

[hmckillop]

One way you could do this is put it in a .sql file and execute from command prompt e.g.

your file contains this

use master
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
        <> 'CREATE_STATISTICS'  
        INSERT  INTO ddl_changelog..DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
          DatabaseName,
          SchemaName,
        SystemUser,
        AppName,
        HostName,
                  tsql ,
        createddate
                ) 
        VALUES  ( 
                   @data.value('(/EVENT_INSTANCE/EventType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 
                              'nvarchar(100)'),
                 system_user , app_name (),host_name(),
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
                              'nvarchar(max)') ,
                getdate()
                ) ; 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
go


use model
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
        <> 'CREATE_STATISTICS'  
        INSERT  INTO ddl_changelog..DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
          DatabaseName,
          SchemaName,
        SystemUser,
        AppName,
        HostName,
                  tsql ,
        createddate
                ) 
        VALUES  ( 
                   @data.value('(/EVENT_INSTANCE/EventType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 
                              'nvarchar(100)'),
                 system_user , app_name (),host_name(),
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
                              'nvarchar(max)') ,
                getdate()
                ) ; 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
go


use otherdb
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
        <> 'CREATE_STATISTICS'  
        INSERT  INTO ddl_changelog..DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
          DatabaseName,
          SchemaName,
        SystemUser,
        AppName,
        HostName,
                  tsql ,
        createddate
                ) 
        VALUES  ( 
                   @data.value('(/EVENT_INSTANCE/EventType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 
                              'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 
                              'nvarchar(100)'),
                 system_user , app_name (),host_name(),
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
                              'nvarchar(max)') ,
                getdate()
                ) ; 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
go

...etc for each db

Save you file as something like 'MyTriggerFile.sql'

to execute this then use osql, or sqlcmd. I am more familiar with osql so will use this.

Open notepad, paste in this

REM @echo off

SET CURDIR=%1
ECHO SET DB_SQLSERVER=192.168.0.1
ECHO SET DB_DB=master
ECHO SET DB_USER=sa
ECHO SET DB_DBPassword=password


ECHO **********Run trigger file ***********
OSQL -S %DB_SQLSERVER% -U %DB_USER% -P %DB_DBPassword% -d master -i "MyTriggerFile.sql" -o "MyTriggerFile.out"

echo ************************************************************************************

Save file as MyBatchFile.bat

make sure you change the server, user and password details

Then from command prompt execute the batch file.

This means anytime you need to execute it on a different server just change the connection string within the batch file and then run.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[glgcag1991]

OK, well then I'll just create an SSIS package to do it.

 

[glgcag1991]

I figured it out using T-SQL!!!! (Though I'll probably do it in SSIS too, just as an exercise.)

I found code online that used the sp_executesql system stored proc and though the single quotes are a bit of a nightmare, it works brilliantly. I added a few "PRINT" statements for meaningful results when the script completes . . .

DECLARE @dataname varchar(255),
@dataname_header varchar(255),
@command NVARCHAR(MAX),
@usecommand VARCHAR(100),
@count int  
SET @command = '';
SET @count = 0;
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases 
WHERE name not in ('master', 'pubs', 'tempdb','msdb','DDL_ChangeLog')
--Do not add this trigger to sys dbs except for model(so all new databases
-- will automatically get the trigger!) and don't include the
--actual db that is tracking the DDL events (DDL_ChangeLog)
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status = 0)
BEGIN

set @count = @count + 1

PRINT '----------BEGIN---------'
PRINT 'Database ' + CAST(@count AS varchar(10)) + ' ' + @dataname;

SET @command = 'DECLARE @my_cmd NVARCHAR(2000); SET @my_cmd = ''CREATE TRIGGER [trgLogDDLEvent] ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''nvarchar(100)'''') 
        <> ''''CREATE_STATISTICS''''  
        INSERT  INTO ddl_changelog..DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
		  DatabaseName,
		  SchemaName,
		SystemUser,
		AppName,
		HostName,
                  tsql ,
		createddate
                ) 
        VALUES  ( 
                   @data.value(''''(/EVENT_INSTANCE/EventType)[1]'''', 
                              ''''nvarchar(100)''''), 
                  @data.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', 
                              ''''nvarchar(100)''''), 
                  @data.value(''''(/EVENT_INSTANCE/ObjectType)[1]'''', 
                              ''''nvarchar(100)''''), 
                  @data.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', 
                              ''''nvarchar(100)''''),
                  @data.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', 
                              ''''nvarchar(100)''''),
				 system_user , app_name (),host_name(),
                  @data.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', 
                              ''''nvarchar(max)'''') ,
				getdate()
                ) ; 

ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE'';

EXEC ' + @dataname + '..sp_executesql @my_cmd'
EXEC (@command);
FETCH NEXT FROM datanames_cursor INTO @dataname;
PRINT '----------END---------'
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor

Enjoy!!! Thanks again for the help!