I was trying to update multiple triggers in a single job step. The solution took me a while to come up with so I thought I would throw it in here. When running mulitple dynamic ALTER TRIGGER statements you quickly discover that you lose context. By using the sp_executesql stored procedure and specifying the database name you can preserv context during each execution.
IF OBJECT_ID('tempdb..#databases') IS NOT NULL DROP TABLE #databases GO CREATE TABLE #databases ( dbname VARCHAR(255)) DECLARE @dbname VARCHAR(255) DECLARE @SQL NVARCHAR(MAX) INSERT INTO #databases SELECT name FROM sys.databases WHERE name LIKE ('Dbname%') AND DATABASEPROPERTY(Name,'IsOffline') = 0 AND DATABASEPROPERTY(Name,'IsInLoad') = 0 AND DATABASEPROPERTY(Name,'IsInRecovery') = 0 WHILE EXISTS(SELECT dbname from #databases) BEGIN SELECT TOP 1 @dbname = dbname FROM #databases SET @SQL = 'ALTER TRIGGER [dbo].[TriggerName] ON ' + @dbname + '.[dbo].[Table_Name] FOR UPDATE AS UPDATE Table_Name SET Col1=''''Value''' EXEC (@dbname + '..sp_executesql N''' + @SQL +'''''') SET @SQL = 'ALTER TRIGGER [dbo].[TriggerName2] ON ' + @dbname + '.[dbo].[TableName2] FOR UPDATE AS UPDATE TableName2 SET Col1=''''Value''' EXEC (@dbname + '..sp_executesql N''' + @SQL +'''''') DELETE #databases where dbname = @dbname END
You can also add whatever else you need in the script. Sometimes the quotes are tough to nail on the first try. Convert those EXEC statements to PRINT so you can view and test the output to make sure you get the syntax correct.