CODEWITHSUNDEEP
sql-serverloopstriggers
Canox
Canox

Reputation: 559

Looping through each table in a database dropping and creating a trigger

I have following two statements executing triggers:

IF EXISTS (SELECT * FROM sys.triggers 
      WHERE Object_ID = Object_ID("Insert_Serverdate_Into_CreatedAt"))
 DROP TRIGGER Insert_Serverdate_Into_CreatedAt
GO

CREATE TRIGGER Insert_Serverdate_Into_CreatedAt ON *myTable*
AFTER INSERT
AS
BEGIN
INSERT INTO *myTable* (CreatedAt) VALUES(GETDATE())
END
GO

IF EXISTS (SELECT * FROM sys.triggers 
      WHERE Object_ID = Object_ID("Insert_Serverdate_Into_UpdatedAt"))
DROP TRIGGER Insert_Serverdate_Into_UpdatedAt
GO

CREATE TRIGGER Insert_Serverdate_Into_UpdatedAt ON *myTable*
AFTER UPDATE
AS
BEGIN
INSERT INTO *myTable* (UpdatedAt) VALUES(GETDATE())
END
GO

Now I have found two possibilities to loop through each table in my database The first one is: 

EXECUTE sp_MSforeachtable @command1 = myCommand

The second one is:

DECLARE @NAME VARCHAR(100)
DECLARE @SQL NVARCHAR(300)

DECLARE CUR CURSOR FOR
   SELECT NAME
   FROM   SYS.TABLES
   WHERE  TYPE = 'U'
     AND SCHEMA_ID = 1

OPEN CUR

FETCH NEXT FROM CUR INTO @NAME

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @SQL = myCommand

  PRINT @SQL
  EXEC Sp_executesql
    @SQL

  FETCH NEXT FROM CUR INTO @NAME
END

CLOSE CUR

DEALLOCATE CUR

The problem here is that these possibilities do not know the keyword GO which I need for executing my commands. Is there a possibility to loop through all tables and execute my statement?

Thank you!

Upvotes: 0

Views: 496

Answers (1)

Peter
Peter

Reputation: 850

Give each of the four parts its own query like this:

DECLARE @NAME VARCHAR(100)
DECLARE @SQL1 NVARCHAR(300)
DECLARE @SQL2 NVARCHAR(300)
DECLARE @SQL3 NVARCHAR(300)
DECLARE @SQL4 NVARCHAR(300)

DECLARE CUR CURSOR FOR
   SELECT NAME
   FROM   SYS.TABLES
   WHERE  TYPE = 'U'
     AND SCHEMA_ID = 1

OPEN CUR

FETCH NEXT FROM CUR INTO @NAME

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @SQL1 = 'IF EXISTS (SELECT * 
                        FROM sys.triggers 
                        WHERE Object_ID = Object_ID(''['+@Name+'_Insert_Serverdate_Into_CreatedAt]'')) 
                 DROP TRIGGER ['+@name+'_Insert_Serverdate_Into_CreatedAt]'
  SET @SQL2 = 'CREATE TRIGGER ['+ @Name + '_Insert_Serverdate_Into_CreatedAt] ON ['+@Name+']
                AFTER INSERT
                AS
                BEGIN
                INSERT INTO ['+@Name+'] (CreatedAt) VALUES(GETDATE())
                END'
  SET @SQL3 = 'IF EXISTS (SELECT * 
                        FROM sys.triggers 
                        WHERE Object_ID = Object_ID(''['+@Name+'_Insert_Serverdate_Into_UpdatedAt]''))
                    DROP TRIGGER ['+@name+'_Insert_Serverdate_Into_UpdatedAt]'
  SET @SQL4 = 'CREATE TRIGGER ['+@Name+'_Insert_Serverdate_Into_UpdatedAt] ON ['+@Name+']
                AFTER UPDATE
                AS
                BEGIN
                INSERT INTO ['+ @Name+'] (UpdatedAt) VALUES(GETDATE())
                END'

  PRINT @SQL1
  EXEC Sp_executesql @SQL1
  PRINT @SQL2
  EXEC Sp_executesql @SQL2
  PRINT @SQL3
  EXEC Sp_executesql @SQL3
  PRINT @SQL4   
  EXEC Sp_executesql @SQL4

  FETCH NEXT FROM CUR INTO @NAME
END

CLOSE CUR

DEALLOCATE CUR

Upvotes: 1

Related Questions