Dynamic, cross database trigger creation

Question

I am trying to dynamically create a number of triggers across two databases but I am having problems with the Create Trigger statement when switching databases

SET @SQL = 'SELECT Name FROM ' + @DB + '.sys.triggers WHERE Name = ' + '''' 
           + @Table + '_DELTATrigger' + ''''
EXEC(@SQL)

IF @@RowCount > 0   
BEGIN
  SET @SQL = 'USE ' + @DB + '; DROP TRIGGER [dbo].[' + @Table + '_DELTATrigger]'
  EXEC(@SQL)
END

SET @SQL = 'CREATE TRIGGER [dbo].[' + @Table + '_DELTATrigger] 
                ON [dbo].[' + @Table + '] 
                AFTER INSERT, UPDATE 
                AS 
                BEGIN 
                  SET NOCOUNT ON 
                  UPDATE [' + @Table + '] 
                  SET [Delta] = 1 
                  FROM inserted 
                  WHERE inserted.[ID] = [' + @Table + '].[ID] 
                END;'               
EXEC (@SQL)

If I run this statement I get the following issue

Error 8197, Level 16, State 4, Procedure tblBuild_DataPlate_DELTATrigger, Line 1
Message: The object 'dbo.tblBuild_DataPlate' does not exist or is invalid for this operation.

Changing the dynamic create trigger to include the USE statement does not work as the Create Trigger has to be the first statement

SET @SQL = 'USE ' + @DB + '; CREATE TRIGGER [dbo].[' + @Table + '_DELTATrigger]
            ON [dbo].[' + @Table + '] 
            AFTER INSERT, UPDATE 
            AS 
            BEGIN 
              SET NOCOUNT ON 
              UPDATE [' + @Table + '] 
              SET [Delta] = 1 
              FROM inserted 
              WHERE inserted.[ID] = [' + @Table + '].[ID] 
            END;'               
EXEC (@SQL)

Error 111, Level 15, State 1, Procedure -, 
Line 1, Message: 'CREATE TRIGGER' must be the first 
statement in a query batch.

You can not fully qualify the object with the database in this case as you get the following error

Error 166, Level 15, State 1, Procedure -, Line 1,
Message: 'CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.

Is there a dynamic way around this?

Did you try CREATE TRIGGER ' + DBName +'[dbo].[' + @Table + '_DELTATrigger] ? — András Ottó
– András Ottó, Commented Nov 29, 2012 at 9:22
Yes. The third error (above) occurs, you can't have the DB prefix in the Create Trigger query — Phil Murray
– Phil Murray, Commented Nov 29, 2012 at 9:24

András Ottó · Accepted Answer · 2012-11-29 09:28:57Z

3

This code below will run the sp_executesql on the selected database (@DB), so with this, you can create the trigger on the rigth place:

SET @SQL = 'EXEC ' + @DB + '..sp_executesql N''
CREATE TRIGGER [dbo].[' + @Table + '_DELTATrigger] 
                            ON [dbo].[' + @Table + '] 
                            AFTER INSERT, UPDATE 
                        AS 
                        BEGIN 
                            SET NOCOUNT ON 

                        END;'''               
            EXEC (@SQL)

answered Nov 29, 2012 at 9:28

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic, cross database trigger creation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related