CreateTriggers
CreateTriggers.sql

Create TimeStamp triggers for all user defined Tables This uses a two pass system. The first pass generates the Triggers that affect the UpdateStamp fields and the second pass creates the triggers that affect the CreateStamp fields. 

Author: Mike Meerding 

/*
 * Create TimeStamp triggers for all user defined Tables
 * This uses a two pass system. The first pass generates the Triggers that affect the UpdateStamp fields and the 
 * second pass creates the triggers that affect the CreateStamp fields.
 */
CREATE PROCEDURE CreateTriggers  AS

/*  Local Variables */
Declare @TableName NVarChar(128)
Declare @Statement NVarChar(512)
Declare @Column_Name NVarChar (128)
Declare @KeyFields NVarChar(256)
Declare @FieldName NVarChar(128)
Declare @Table_ID Int
Declare @PassCount Int

Set @PassCount = 0

WHILE(@PassCount <= 1)
Begin
 /* Cursor declaration to figure out names of all user tables */
 Declare AllTables CURSOR 
  For Select Name from sysobjects 
   Where xtype = "U"
 Open AllTables

 /* Scan the sysobjects table for names of all user tables */
 FETCH NEXT FROM AllTables Into @TableName
 While (@@Fetch_Status = 0 )
 Begin
  Print 'Checking Table ' + @TableNAME

  Print 'Finding Primary Key Fields...'
 
  Select @Table_ID = Object_ID(@TableName)

  /* Does this table have the required fields ? */
  if @PassCount = 0
   Set @FieldName = 'UpdateStamp'
  else
   Set @FieldName = 'CreateStamp'

  exec sp_columns @TABLE_NAME = @TableName, @Column_name = @FieldName

  IF (@@RowCount = 1)
  BEGIN

   /* Cursor declaration to figure out names of key fields for a specific table (swiped from sp_pkeys) */  
   Declare ThisTable CURSOR 
   FOR
    SELECT  COLUMN_NAME = convert(sysname, c.name)  
    From sysindexes i, syscolumns c, sysobjects o, syscolumns c1
    Where 
     o.id = @Table_ID and 
     o.id = c.id and 
     o.id = i.id and 
     (i.status & 0x800) = 0x800 and 
     c.name = index_col (@TableName, i.indid, c1.colid) and 
     c1.colid <= i.keycnt and 
     c1.id = @table_id 
 
   Open ThisTable

   /* Generate SQL string that will create the trigger which creates the timestamps */
   Set @Statement = ''  
   if exists (select * from sysobjects where id = object_id(N'dbo.' + @FieldName+'_' + @TableName) and OBJECTPROPERTY(id, N'IsTrigger') = 1)
   BEGIN
    Set @Statement = 'drop trigger dbo.' + @FieldName+'_' + @TableName  + char(13)
    Print char(13)
    Print @Statement
    Execute sp_executesql  @Statement  
    Print char(13)
   END
  

   Set @Statement = 'Create Trigger dbo.' + @FieldName+'_'  + @TableName + ' ON ' + @TableName + char(13) 
   Set @Statement = @Statement + ' FOR '
   IF @PassCount = 0
    Set @Statement = @Statement + ' Update ' 
   else
    Set @Statement = @Statement + ' Insert  ' 
   Set @Statement = @Statement + ' AS ' + char(13) 
   Set @Statement = @Statement + ' BEGIN ' + char(13) 
   Set @Statement = @Statement + '     UPDATE ' + @TABLENAME + char(13) 
   Set @Statement = @Statement + '     SET ' + @FieldName + ' = GetDate() ' + char(13) 

   /* scan the system tables to build a list of primary keyfield names */
   Set @KeyFields = ""
   FETCH NEXT FROM ThisTable into @COLUMN_NAME
   WHILE (@@FETCH_STATUS= 0)
   BEGIN
    IF @KeyFields = ""
    BEGIN
     Set @KeyFields = @COLUMN_NAME
     Set @Statement = @Statement + '     WHERE '  + @COLUMN_NAME +' in (SELECT ' + @COLUMN_NAME +  ' FROM INSERTED)' + char(13) 
    END  
    ELSE
    BEGIN  
     Set @KeyFields = @KeyFields + ", " + @COLUMN_NAME
     Set @Statement = @Statement + '     AND '  + @COLUMN_NAME +' in (SELECT ' + @COLUMN_NAME +  ' FROM INSERTED)' + char(13) 
    END

    FETCH NEXT FROM ThisTable into @COLUMN_NAME
   END

   Print 'Keyfields are :- ' + @KeyFields

   Close ThisTable
   Deallocate ThisTable

   Set @Statement = @Statement + ' END' + char(13)
   Print char(13)
   Print @Statement

   If @KeyFields != ""
    Execute sp_executesql  @Statement  
   Print char(13)
  END

  FETCH NEXT FROM AllTables Into @TableName
 END
 Set @PassCount = @PassCount + 1

 Close AllTables
 Deallocate AllTables

End