How-to create a table and its according history table in tsql

The main principle -

recreate the table from ssms
with TableNameHistory
add A TableNameHistoryId with the identity
add an Action Column - specifies what is the action performed on the table to track
add an SqlLogin Column - specifies the sql login performing the action
add an UpdateWhen column - specifies the time of the update ( here Update means the UPDATE , DELETE , INSERT actions )

Copy paste this triggers and replace the name of the "Feature" table with the name of your table
+ replace the corresponing columns.

The whole thing could wrap up in a single DDL trigger for CREATE_TABLE. If you have done it , please share a code ...

USE [GenApp]

GO

/****** Object:  Table [dbo].[FeatureHistory]    Script Date: 09/08/2009 10:02:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FeatureHistory](

[FeatureHistoryId] [int] IDENTITY(1,1) NOT NULL,

[FeatureId] [int] NOT NULL,

[Vote] [int] NULL,

[Key] [varchar](200) NULL,

[Title] [varchar](2000) NOT NULL,

[Description] [varchar](2000) NOT NULL,

[GenAppApplicationId] [int] NULL,

[FeatureHistoryTagId] [int] NULL,

[Action] [varchar](50) NULL,

[SqlLogin] [varchar](200) NULL,

[UpdateWhen] [datetime] NOT NULL,

CONSTRAINT [PK_FeatureHistory] PRIMARY KEY CLUSTERED 

(

[FeatureHistoryId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

ALTER TABLE [dbo].[FeatureHistory] ADD  CONSTRAINT [DF_FeatureHistory_UpdateWhen]  DEFAULT (getdate()) FOR [UpdateWhen]

GO

USE [GenApp]

GO

/****** Object:  Table [dbo].[Feature]    Script Date: 09/08/2009 10:02:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Feature](

[FeatureId] [int] IDENTITY(1,1) NOT NULL,

[Vote] [int] NULL,

[Key] [varchar](200) NULL,

[Title] [varchar](2000) NOT NULL,

[Description] [varchar](2000) NOT NULL,

[GenAppApplicationId] [int] NULL,

[FeatureTagId] [int] NULL,

CONSTRAINT [PK_Feature] PRIMARY KEY CLUSTERED 

(

[FeatureId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Trigger [dbo].[trig_AfterDeleteOnFeature]    Script Date: 09/08/2009 10:02:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Yordan Georgiev>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE TRIGGER [dbo].[trig_AfterDeleteOnFeature]    

ON  [dbo].[Feature]

for Delete

AS 

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Insert into FeatureHistory 

( 

[FeatureId] , 

[Vote],

[Key],

[Title],

[Description],

[GenAppApplicationId],

[FeatureHistoryTagId],

[SqlLogin] , 

[Action],

[UpdateWhen]

)

select

[FeatureId] , 

[Vote],

[Key],

[Title],

[Description],

[GenAppApplicationId],

[FeatureTagId],

user_name(),

'DELETE',

getdate()

from Deleted

END

GO

/****** Object:  Trigger [dbo].[trig_AfterInsertOnFeature]    Script Date: 09/08/2009 10:02:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Yordan Georgiev>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE TRIGGER [dbo].[trig_AfterInsertOnFeature]    

ON  [dbo].[Feature]

AFTER insert

AS 

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

insert     into FeatureHistory 

( 

[FeatureId] , 

[Vote],

[Key],

[Title],

[Description],

[GenAppApplicationId],

[FeatureHistoryTagId],

[Action],

[SqlLogin],

[UpdateWhen]

)

select

[FeatureId] , 

[Vote],

[Key],

[Title],

[Description],

[GenAppApplicationId],

[FeatureTagId],

'INSERT',

user_name(), 

getdate()

from Feature where FeatureId in (select FeatureId from inserted)

select current_user

END

GO

/****** Object:  Trigger [dbo].[trig_AfterUpdateOnFeature]    Script Date: 09/08/2009 10:02:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Yordan Georgiev>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE TRIGGER [dbo].[trig_AfterUpdateOnFeature]    

ON  [dbo].[Feature]

AFTER UPDATE

AS 

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

insert     into FeatureHistory 

(

[FeatureId] ,  

[Vote],

[Key],

[Title],

[Description],

[GenAppApplicationId],

[FeatureHistoryTagId],

[SqlLogin] , 

[Action],

[UpdateWhen]

)

select

[FeatureId] , 

[Vote],

[Key],

[Title],

[Description],

[GenAppApplicationId],

[FeatureTagId],

user_name() , 

'UPDATE'  , 

getdate()

from Feature where FeatureId in (select FeatureId from inserted)

END

GO