/*
 * 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