June 14, 2011 at 9:46 pm
Hi All,
I am having a bit of difficulty and hoping you all can help me! I'm trying to automate the process of creating a database trigger whenever a new database is created.
I have explored some options - and the ones that I am NOT able to use are sp_MSforeachdb (as it is not officially documented), nor just creating the trigger on the Model database.
What my thoughts are so far is to create a temporary table that contains the user databases and loop through them one at a time, determining if a trigger has been created on it, and if it hasnt to do so.
I'm having a bit of a problem though changing into the context of the database (USE ['+@database_name+']) and then performing the IF NOT EXISTS CREATE TRIGGER statement. I'm attempting to print out the statement, however that is not helping either as it will not print when there is a variable name for the database name.
Has anyone else done this before?
Thanks!
June 15, 2011 at 12:30 pm
chelta (6/14/2011)
...I'm attempting to print out the statement, however that is not helping either as it will not print when there is a variable name for the database name.
Why not? Here is one way:
DECLARE @My VARCHAR(max)
SET @My = ''
SELECT
@My ='USE [' + Database_Name + '] IF NOT EXISTS CREATE TRIGGER ... ' + @My
FROM
(
SELECT 'Database1' as Database_Name
UNION ALL SELECT 'Database2'
UNION ALL SELECT 'Database3'
) x
PRINT @My
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 15, 2011 at 7:05 pm
What about something like the below? Create a trigger at the server level that creates a trigger on the new database. Your trigger definition that would go on the user database would exist in where mytrigger is. The only tricky thing is escaping quotes correctly since you have to do a nested exec.
IF EXISTS (SELECT 1 FROM sys.server_triggers WHERE name='new_USER_DB')
BEGIN
DROP TRIGGER new_USER_DB on ALL SERVER
END
GO
CREATE TRIGGER new_USER_DB
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @trigger_SQL nvarchar(4000)
declare @databasename varchar(256)
set @databasename = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
SELECT @trigger_SQL = N'use [' + @databasename + '] ' +
N' exec sp_executesql N''CREATE TRIGGER mytrigger on database FOR CREATE_PROCEDURE AS PRINT ''''WHY DID YOU CREATE A STORED PROCEDURE?'''' '' '
exec sp_executesql @trigger_SQL
GO
June 15, 2011 at 11:19 pm
If you just add it to the Model database, it'll be there for every new database you create. Make it a good one, though, because it will also appear on TempDB the next you cycle the service. Of course, you could have a startup proc that drops it from TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2011 at 7:14 pm
Hi All,
Ideally we want to be able to 'pick' what databases its installed on and automate it. There may be new user databases created that we dont want it enabled on, so the script needs to be robust enough to pick up on that.
This is why i cant create it a serverwide trigger, or place it on model. We dont want captured all the changes going through tempdb either, because itd just produce too much 'noise'.
I think i have the basics - however placing the command in a SET @variable and then EXEC(@variable) is doing my head in!
For instance:
SELECT@DatabaseName = @EventData.value(''(/EVENT_INSTANCE/DatabaseName)[1], ''varchar(256)''),
It keeps erroring as:
Incorrect syntax near '('.
Can anyone suggest how this can be 'fixed' so i have the quotes in the right spots? I've tried most combinations I can think of! ;(
June 17, 2011 at 4:04 am
chelta (6/16/2011)
Hi All,Ideally we want to be able to 'pick' what databases its installed on and automate it. There may be new user databases created that we dont want it enabled on, so the script needs to be robust enough to pick up on that.
This is why i cant create it a serverwide trigger, or place it on model. We dont want captured all the changes going through tempdb either, because itd just produce too much 'noise'.
I think i have the basics - however placing the command in a SET @variable and then EXEC(@variable) is doing my head in!
For instance:
SELECT@DatabaseName = @EventData.value(''(/EVENT_INSTANCE/DatabaseName)[1], ''varchar(256)''),
It keeps erroring as:
Incorrect syntax near '('.
Can anyone suggest how this can be 'fixed' so i have the quotes in the right spots? I've tried most combinations I can think of! ;(
Looks like @EventData is an XML variable and I don't work with XML much. I'm pretty sure, though, that you must use single quotes rather than double.
Looking at the previous examples in this thread, it also looks like you're missing some parenthesis between @EventData and ".".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2011 at 4:10 am
My recommendation, at this point, would be to write the trigger you want for one database and post it. We'll help you write code to distribute it to a list of desired databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2011 at 7:24 am
I agree with Jeff. Post up a sample of your trigger and your requirements and maybe we could help you more.
June 19, 2011 at 9:05 pm
Hi All,
Thanks - I'll post the 'while' loop so you can see what I'm attempting to do 😉
WHILE (SELECT count(*) FROM #database_trigger_check WHERE processed = 0) != 0
BEGIN
SET @database_name = (SELECT TOP 1 [database_name] FROM #database_trigger_check WHERE processed = 0)
SET @trigger_statement=N'
USE ['+@database_name+']
IF NOT EXISTS(SELECT * FROM sys.triggers WHERE parent_class_desc = ''DATABASE'' AND name = N''TriggerName'')
CREATE TRIGGER [dbo].[TriggerName]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON
DECLARE @DatabaseName nvarchar(256),
@EventType nvarchar(256),
@SchemaName nvarchar(256),
@ObjectName nvarchar(256),
@ObjectType nvarchar(256),
@SQLCommand nvarchar(2000),
@EventData xml
SET @EventData = EVENTDATA()
SELECT@DatabaseName = @EventData.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''varchar(256)''),
@EventType = @EventData.value(''(/EVENT_INSTANCE/EventType)[1]'',''nvarchar(256)''),
@SchemaName = @EventData.value(''(/EVENT_INSTANCE/SchemaName)[1]'',''nvarchar(256)''),
@ObjectName = @EventData.value(''(/EVENT_INSTANCE/ObjectName)[1]'',''nvarchar(256)''),
@ObjectType = @EventData.value(''(/EVENT_INSTANCE/ObjectType)[1]'',''nvarchar(256)''),
@SQLCommand = @EventData.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''nvarchar(2000)'')
-- 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 [AuditDB].[dbo].[DDLChanges] ([DatabaseName],[EventType],[SchemaName],[ObjectName],[ObjectType],[EventDate],[SystemUser],[CurrentUser],[OriginalUser],[SQLCommand])
SELECT @DatabaseName,@EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@SQLCommand
END
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
DISABLE TRIGGER [TriggerName] ON DATABASE
ENABLE TRIGGER [TriggerName] ON DATABASE)
PRINT ''TRIGGER Created on '+@database_name+'
ELSE PRINT ''TRIGGER Already Created on '+@database_name+'
'
EXEC(@trigger_statement)
UPDATE #database_trigger_check SET processed = 1 WHERE database_name = @database_name
SET @database_name = ''
SET @trigger_statement = ''
END
So I have a table that populates what databases I want to create the trigger on. It has a processed field so I can loop through it. The loop of the database name works fine, its where it gets up to the SET @trigger_statement thats failing - I'm confident its because I'm not putting the ''s in the right spots - so any help would be very much appreciated!
June 20, 2011 at 9:53 am
I noticed a few issues that I cleaned up in what I posted. You didn't close your print statements correctly, the IF statement was incorrect, and because this is running from a create_database trigger we don't need the while loop or has been processed logic so I took that out as well. You could check the name after pulling out the database name from the event data, if this database needs the trigger created. I posted what I was using to test that the trigger was being created correctly on the new database. I did not test the functionality of the trigger you want to create it self, but I'll let you play with that.
Hope this helps
IF EXISTS (SELECT 1 from sys.server_triggers where name='new_user_DB')
BEGIN
DROP TRIGGER new_USER_DB
ON ALL SERVER
END
GO
CREATE TRIGGER new_USER_DB
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @trigger_SQL nvarchar(4000)
declare @databasename varchar(256)
set @databasename = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
SET @trigger_SQL=N'
USE ['+@databasename+']
exec sp_executesql N''
CREATE TRIGGER [TriggerName]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON
DECLARE @DatabaseName nvarchar(256),
@EventType nvarchar(256),
@SchemaName nvarchar(256),
@ObjectName nvarchar(256),
@ObjectType nvarchar(256),
@SQLCommand nvarchar(2000),
@EventData xml
SET @EventData = EVENTDATA()
SELECT @DatabaseName = @EventData.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''varchar(256)''''),
@EventType = @EventData.value(''''(/EVENT_INSTANCE/EventType)[1]'''',''''nvarchar(256)''''),
@SchemaName = @EventData.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''',''''nvarchar(256)''''),
@ObjectName = @EventData.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''',''''nvarchar(256)''''),
@ObjectType = @EventData.value(''''(/EVENT_INSTANCE/ObjectType)[1]'''',''''nvarchar(256)''''),
@SQLCommand = @EventData.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''nvarchar(2000)'''')
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 [AuditDB].[dbo].[DDLChanges] ([DatabaseName],[EventType],[SchemaName],[ObjectName],[ObjectType],[EventDate],[SystemUser],[CurrentUser],[OriginalUser],[SQLCommand])
SELECT @DatabaseName,@EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@SQLCommand
END''
'
EXEC(@trigger_SQL)
GO
--Test the new trigger
CREATE DATABASE TestNewTrigger
June 20, 2011 at 5:22 pm
Hi,
Thanks for all the help guys - I think I've FINALLY got it worked out! Had to use a ridiculous amount of '''s, but I thought that was my problem and for the most part it was!
Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply