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

Question

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!

Whatever you think you are doing with this trigger, rethink it. It looks like you are simply attempting to add a `CREATED_AT` and `UPDATED_AT` timestamp for every row. A trigger like this on every table is a sure way to kill performance on your database. You'd be much better off using two columns (one for created_date, one for modified_date) with a default value of `GETDATE()`. Also see https://stackoverflow.com/questions/13171335/computed-column-with-current-datetime/13171375#13171375 — SchmitzIT, Jun 15 '17 at 07:23
I've already added two columns which are from type GetDate(). Now I need to execute the triggers for creating and modifying — Canox, Jun 15 '17 at 07:34
I still think you should at the very least reconsider the `INSERT` trigger, as that can easily be solved with a `DEFAULT`. I personally would handle the `UPDATE` in the statement, but a trigger could be used to accomplish that. Also see https://stackoverflow.com/questions/7737945/how-to-create-trigger-for-auto-update-modified-date-with-sql-server-2008 — SchmitzIT, Jun 15 '17 at 07:51

Peter · Accepted Answer · 2017-06-15T08:32:40.713

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

I'm getting: Msg 207, Level 16, State 1, Line 2 Invalid column name 'Insert_Serverdate_Into_CreatedAt'. Same for the other trigger names — Canox, Jun 15 '17 at 07:29
This seems to work partially. In some cases I get Syntax errors like `Incorrect Syntax near (` or `Incorrect Syntax near VALU`. Why? — Canox, Jun 15 '17 at 08:00
Ok I found out. Some of them are just because the statement had to less chars. But what do I do with tables that have spaces or somethin like that in their name? this is not an acceptable trigger name... — Canox, Jun 15 '17 at 08:26
You can uses spaces along as you use square brackets around the table and trigger names. I've updated the answer. — Peter, Jun 15 '17 at 08:33

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

1 Answers1