September 25, 2008 at 8:04 am
Ok ... I'm struggling to find a way to do this without having an exec wrapped around an exec. The code is already ugly enough having this in dynamic, but until I change the way I sync all my admin db's, this is my best bet. Modified a known process out there slightly to capture DDL events, and I need to push the trigger to all databases.
1) Why can you not check for trigger existance with OBJECT_ID? Look how I had to find it, not how I would normally do it.
2) Without dropping/recreating the object every time, how can I use a database and create an object?
Thanks in advance
SET NOCOUNT ON
-- Setup DDLChangeLog Table
IF OBJECT_ID('DDLChangeLog','U') IS NULL
BEGIN
CREATE TABLE [dbo].[DDLChangeLog]
(
[LogId]INTIDENTITY(1,1) CONSTRAINT PK_DDLChangeLog PRIMARY KEY CLUSTERED
,[DatabaseName] VARCHAR(256)
,[EventType]VARCHAR(50)
,[ObjectName]VARCHAR(256)
,[ObjectType]VARCHAR(25)
,[SqlCommand]VARCHAR(MAX)
,[EventDate]DATETIME CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (GETDATE())
,[LoginName]VARCHAR(256)
,[ProgramName]VARCHAR(256)
)
GRANT INSERT ON [dbo].[DDLChangeLog] TO [public]
END
-- Push Trigger to all databases
IF OBJECT_ID('tempdb.dbo.#systriggers') IS NOT NULL
DROP TABLE #systriggers
SELECT TOP 0 * INTO #systriggers FROM [sys].[triggers]
DECLARE
@DBNameSYSNAME
,@LoggingDBNameSYSNAME
SET @LoggingDBName = 'admin'
DECLARE #dbs CURSOR STATIC LOCAL FOR
SELECT [name] FROM [master].[sys].[databases]
WHERE [source_database_id] IS NULL
AND [name] != 'tempdb'
AND [name] = 'admin'
ORDER BY [name]
OPEN #dbs
FETCH NEXT FROM #dbs INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Lame way of checking to see if trigger exists as OBJECT_ID does not work for triggers
TRUNCATE TABLE #systriggers
INSERT INTO #systriggers
EXEC('USE [' + @DBName + '] SELECT * FROM [sys].[triggers]')
IF NOT EXISTS(SELECT * FROM #systriggers WHERE [parent_class_desc] = 'DATABASE' AND [name] = N'DDLChange_trg')
BEGIN
EXEC('
USE [' + @DBName + ']
GO
/*******************************************************************************************************
**Name:dbo.DDLChange_trg
**Desc:Trigger to capture all database DDL events
**Auth:Adam Bean (SQLSlayer.com)
Modified from: http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
**Date:2008.09.24
*******************************************************************************
**Change History
*******************************************************************************
**Date:Author:Description:
**-------------------------------------------------------
********************************************************************************************************/
CREATE TRIGGER [DDLChange_trg] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE
@DataXML
,@ProgramNameVARCHAR(256)
SELECT @ProgramName = [program_name] FROM [sys].[dm_exec_sessions] WHERE [session_id] = @@SPID
SET @Data = EVENTDATA()
INSERT INTO [' + @LoggingDBName + '].[dbo].[DDLChangeLog]
([DatabaseName], [EventType], [ObjectName], [ObjectType], [SqlCommand], [LoginName], [ProgramName])
VALUES
(
@Data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''VARCHAR(256)'')
,@Data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''VARCHAR(50)'')
,@Data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''VARCHAR(256)'')
,@Data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''VARCHAR(25)'')
,@Data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''VARCHAR(MAX)'')
,@Data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''VARCHAR(256)'')
,@ProgramName
)
SET NOCOUNT OFF
')
END
ELSE
PRINT 'Trigger already exists in database: ' + @DBName + ''
FETCH NEXT FROM #dbs INTO @DBName
END
CLOSE #dbs
DEALLOCATE #dbs
SET NOCOUNT OFF
September 25, 2008 at 8:22 am
2. use xxxdb go create trigger.
Not sure on #1
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2008 at 8:24 am
Steve, check where I start the EXEC ... that is what I'm doing. Unfortunately it does not work.
September 25, 2008 at 9:25 am
Why not use sp_msforeachdb?
Would allow you to get that created....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 9:32 am
I was able to resolve it with a double exec ...
January 14, 2010 at 7:43 pm
Could you give some specifics on your solution. (double exec)
January 18, 2010 at 6:57 am
Simply wrap another EXEC around the existing EXEC. Just make sure you have all your single quotes setup properly.
September 15, 2010 at 4:12 pm
Adam
I am trying the same but getting hard time to change the single quotees i have in the trigger while wrapping double EXEC
September 16, 2010 at 7:06 am
Tara-1044200 (9/15/2010)
AdamI am trying the same but getting hard time to change the single quotees i have in the trigger while wrapping double EXEC
Usually easiest way is to do a find/replace on the single quote.
October 13, 2011 at 9:37 am
Hi Adam ,
I am working on the same requirement and having the same error
"'CREATE TRIGGER' must be the first statement in a query batch."
I appreciate if you could post the amended script.
Regards
Murali
October 13, 2011 at 9:47 am
Just add a GO on the line before the CREATE TRIGGER.
October 13, 2011 at 9:57 am
Hi ,
I am using the following code :
DECLARE @DBNAME VARCHAR(128)
DECLARE DBLIST_CURSOR CURSOR FOR
SELECT NAME FROM sys.databases WHere name not in ( 'msdb','master','model','tempdb')
OPEN DBLIST_CURSOR
FETCH NEXT FROM DBLIST_CURSOR INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @ssql VARCHAR(8000)
Set @ssql = 'USE [' + @DBNAME + ']
go
create trigger DDLChanges
on database
for
CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_STATISTICS,DROP_STATISTICS, UPDATE_STATISTICS,
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,
CREATE_TRIGGER,ALTER_TRIGGER, DROP_TRIGGER,
CREATE_USER,ALTER_USER, DROP_USER,
CREATE_VIEW,ALTER_VIEW, DROP_VIEW
as
set nocount on
declare @data xml
set @data = EVENTDATA()
insert into DBA_Maint.dbo.DDLChangeLog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''varchar(256)''),
@data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''varchar(50)''),
@data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''varchar(256)''),
@data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''varchar(25)''),
@data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''varchar(max)''),
@data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''varchar(256)'')
)
'
EXEC( @SSQL)
FETCH NEXT FROM DBLIST_CURSOR INTO @DBNAME
END
Close DBLIST_CURSOR
DEALLOCATE DBLIST_CURSOR
Error:
Msg 111, Level 15, State 1, Line 2
'CREATE TRIGGER' must be the first statement in a query batch.
October 13, 2011 at 9:59 am
Change GO for ;
October 13, 2011 at 10:13 am
No luck mate
October 13, 2011 at 10:23 am
Looks like from the previous posts that you need to add another EXEC() after the use.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply