1

I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works on it's on but I can't get it to work with sp_foreachdb so I'm guessing I've missed a single quote. However, I'm struggling to find the error. Any help is greatly appreciated.

EXEC master.dbo.sp_foreachdb
'
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name = 
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN   
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml   XML
DECLARE @SchemaName     SYSNAME
DECLARE @ObjectName     SYSNAME
DECLARE @EventType      SYSNAME 

SET @EventDataXml = EVENTDATA()

SELECT  
@EventType  = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''') 

INSERT [ServerAdmin].[dbo].[ServerChangeLog] 
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT  GETDATE()
        , SUSER_NAME()
        , HOST_NAME()
        , PROGRAM_NAME()
        , @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
        , @EventType
        , @SchemaName
        , @ObjectName
        , @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
        , @EventDataXml
''
)
END
'

The error I'm getting is

Msg 102, Level 15, State 1, Procedure Database_DDL_Audit, Line 40 [Batch Start Line 0] Incorrect syntax near '@EventDataXml'.

2

You are missing an END. See updated as below:

Note: Use Aaron's version of sp_msforeachdb

declare @sql nvarchar(max) 

set @sql = '
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name = 
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN   
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml   XML
DECLARE @SchemaName     SYSNAME
DECLARE @ObjectName     SYSNAME
DECLARE @EventType      SYSNAME 

SET @EventDataXml = EVENTDATA()

SELECT  
@EventType  = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''') 

INSERT [ServerAdmin].[dbo].[ServerChangeLog] 
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT  GETDATE()
        , SUSER_NAME()
        , HOST_NAME()
        , PROGRAM_NAME()
        , @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
        , @EventType
        , @SchemaName
        , @ObjectName
        , @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
        , @EventDataXml
END
''
)
END
'
print @sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.