Loop and Create Triggers in each user database

Question

Loop and Create Triggers in each user database

jzhang-1098563

SSC Enthusiast

Points: 131
More actions
November 17, 2010 at 10:15 am

#243055

I am trying to complie a script that can automatically loop and create a database level trigger on each user database.
Here is the script:
USE master;
SET NOCOUNT ON;
DECLARE @db_name nvarchar(50);
DECLARE @drop_sql nvarchar(1000);
DECLARE @trg1_sql nvarchar(4000);
SET @drop_sql = 'IF EXISTS ( SELECT * FROM sys.triggers
WHERE name = ''trgLogDatabaseSecurityEvents'' )
DROP TRIGGER trgLogDatabaseSecurityEvents ON DATABASE; ' ;
DECLARE cur_db_name CURSOR FOR
SELECT name FROM sys.databases
WHERE database_id>4
ORDER BY database_id;
OPEN cur_db_name;
FETCH NEXT FROM cur_db_name INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @drop_sql = ' USE ' + @db_name + '; ' + @drop_sql;
EXEC sp_executesql @drop_sql;
SET @trg1_sql = 'CREATE TRIGGER trgLogDatabaseSecurityEvents ON DATABASE
FOR DDL_DATABASE_SECURITY_EVENTS
AS
DECLARE @data XML ;
DECLARE @hostName nvarchar(100);
DECLARE @audit_msg varchar(255);
DECLARE @EventType varchar(100);
SET @data = EVENTDATA() ;
SET @EventType = @data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''nvarchar(100)'');
IF EXISTS (SELECT * FROM Master.dbo.[sysobjects] WHERE name = ''DDLSecurityLog'' AND xtype =''U'')
select @hostName = host_name FROM sys.dm_exec_sessions s
where s.session_id = @data.value(''(/EVENT_INSTANCE/SPID)[1]'',''int'');
INSERT INTO master..DDLSecurityLog
(
EventType,
ObjectName,
ObjectType,
DatabaseName,
tsql , SPID, hostname
)
VALUES
(
@EventType,
@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/TSQLCommand)[1]'', ''nvarchar(max)'') ,
@data.value(''(/EVENT_INSTANCE/SPID)[1]'', ''int''),
@hostName
) ;' ;
-- SELECT @trg1_sql;
EXEC sp_executesql @trg1_sql;
FETCH NEXT FROM cur_db_name INTO @db_name;
END;
CLOSE cur_db_name;
DEALLOCATE cur_db_name;
The problem here is I can't switch database before running the create trigger statement.
Because "Create Trigger...needs to be the first statement in a script".
So the trigger will always be create in master db.
Any idea how to realize this?
Thanks.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

One way out of this is to embed another exec executesql in your @trg1_sql value. For example, if you have:

set @trg1_sql = 'use ' + @dbname +
';exec sp_executesql N''CREATE TRIGGER trgLogDatabaseSecurityEvents
ON DATABASE etc''';

and then

exec sp_executesql @trg1_sql

then the create trigger part will be a separate batch executing in the context of the correct database. If this is not a convenient solution because you have to double up on your single quotes once more then take a look at the sp_MSForEachDB procedure. It it implemented as a cursor loop through all user databases, so you code will become simpler. Here is the link to discussion: http://www.sqlservercentral.com/Forums/Topic517024-146-1.aspx

Oleg