create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID
Steps:
1) Paste script below into Query Analyzer
2) set Query Analyzer results to "text" (not "grid"
3) set @v_Tablename = 'myTableName' below
4) set @v_postfix = '_ID' below (not 'myTableName_ID' )
5) run script with no errors (script produces new script)
6) paste text results (new script) into query Query Analyzer
7) run script with no errors new table and triggers created
Set NoCount On
Go
declare @v_Tablename varchar(100)--this is for setting the table name to build history tables for
declare @v_postfix varchar(20) --this is for setting the @v_postfix or _key etc
declare @v_grantto varchar(50) --this for grant to statement
select @v_Tablename = 'myTableName'
select @v_postfix = '_ID'
select @v_grantto = 'dbo'
Select 'Print ' + '''' + 'Drop Hist Tables' + '''' + Char (13) + 'Go'
Select 'if exists (select * from dbo.sysobjects where id = object_id(N''dbo.' + Upper (Rtrim (Name)) + '_Audit'') and OBJECTPROPERTY(id, N''IsTable'') = 1) Drop Table dbo.' + Upper (Rtrim (Name)) + '_Audit ' +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Create Audit Tables' + '''' + Char (13) + 'Go'
Select 'Select * Into dbo.' + Upper (Rtrim (Name)) + '_AUDIT From ' + Upper (Rtrim (Name)) +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Add Posted fields' + '''' + Char (13) + 'Go'
Select 'Alter Table dbo.' + Upper (Rtrim (Name)) + '_AUDIT ' +
Char (13) + ' Add POSTED_ACTION VarChar (10) Null Default ' + '''' + 'INSERT' + ''',' +
Char (13) + ' POSTED_BY VarChar (30) Null Default Host_Name (),' +
Char (13) + ' POSTED_DATE VarChar (30) Null Default GetDate ()' +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Drop Identity field' + '''' + Char (13) + 'Go'
Select 'Alter Table dbo.' + Upper (Rtrim (Name)) + '_AUDIT ' +
Char (13) + ' Drop Column ' + Upper (Rtrim (Name)) + @v_postfix +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Add Identity field' + '''' + Char (13) + 'Go'
Select 'Alter Table dbo.' + Upper (Rtrim (Name)) + '_AUDIT ' +
Char (13) + ' Add ' + Upper (Rtrim (Name)) + '_AUDIT_ID Integer Identity,' +
Char (13) + ' ' + Upper (Rtrim (Name)) + @v_postfix + ' Integer' +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Truncate table' + '''' + Char (13) + 'Go'
Select 'Truncate Table dbo.' + Upper (Rtrim (Name)) + '_AUDIT ' +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Grant all ' + '''' + Char (13) + 'Go'
Select 'Grant All on dbo.' + Upper (Rtrim (Name)) + '_AUDIT To ' + @v_grantto +
Char (13) + 'Go'
From Sysobjects
Where Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Drop Insert Trigger' + '''' + Char (13) + 'Go'
Select 'if exists (select * from dbo.sysobjects where id = object_id(N''dbo.TrgInsAudit_' + Upper (Rtrim (A.Name)) + ''') and OBJECTPROPERTY(id, N''IsTrigger'') = 1) Drop Trigger dbo.TrgInsAudit_' + Upper (Rtrim (A.Name)) +
Char (13) + 'Go'
From sysobjects as A
Where A.Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Create Insert trigger' + '''' + Char (13) + 'Go'
Declare @V_TblId As Integer
Declare @V_TblCtr As Integer
Declare @V_ColId As Integer
Declare @V_ColCtr As Integer
Declare @V_TblName As VarChar (30)
Declare @V_ColName As VarChar (35)
Declare @V_Fld As VarChar (7500)
Declare @V_Sel As VarChar (7500)
Declare @V_Sql As VarChar (8000)
Declare CurReadTbl Cursor Fast_Forward Read_Only For
Select Distinct A.Id,
Upper (Rtrim (SubString (A.Name, 1, 30)))
From sysobjects as A
Where A.Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Set @V_TblCtr = 0
Set @V_Sql = ''
Open CurReadTbl
Fetch Next From CurReadTbl
Into @V_TblId,
@V_TblName
While @@Fetch_Status = 0
Begin
Set @V_TblCtr = @V_TblCtr + 1
Set @V_ColCtr = 0
Set @V_Sql = 'Create Trigger dbo.TrgInsAudit_'
Set @V_Fld = ''
Set @V_Sel = ' Select '
Declare CurReadCol Cursor Fast_Forward Read_Only For
Select Distinct A.ColId,
SubString (A.Name, 1, 35)
From syscolumns as A
Where A.Id = @V_TblId
And A.Name Not Like '%PICTURE%'
Order By 2, 1
Set @V_Sql = @V_Sql + @V_TblName + Char (13) + ' On dbo.' + @V_TblName + ' For Insert As ' + Char (13) + ' Begin' + Char (13) + ' Insert Into ' + @V_TblName + '_AUDIT ('
Open CurReadCol
Fetch Next From CurReadCol
Into @V_ColId,
@V_ColName
While @@Fetch_Status = 0
Begin
Set @V_ColCtr = @V_ColCtr + 1
Set @V_Fld = @V_Fld + @V_ColName + ', '
Set @V_Sel = @V_Sel + 'Ins.' + @V_ColName + ', '
Fetch Next From CurReadCol
Into @V_ColId,
@V_ColName
End
Set @V_Fld = @V_Fld + Upper ('Posted_Action, Posted_By, Posted_Date')
Set @V_Sel = @V_Sel + '''' + 'INSERT' + '''' + ', Host_Name (), GetDate () From inserted As Ins'
Set @V_Sql = @V_Sql + Rtrim (@V_Fld) + ')' + Char (13) + @V_Sel
Set @V_Sql = @V_Sql + Char (13) + ' End' + Char (13) + 'Go'
Print @V_Sql
Close CurReadCol
DeAllocate CurReadCol
Fetch Next From CurReadTbl
Into @V_TblId,
@V_TblName
End
Close CurReadTbl
DeAllocate CurReadTbl
Select 'Print ' + '''' + 'Drop Update trigger' + '''' + Char (13) + 'Go'
Select 'if exists (select * from dbo.sysobjects where id = object_id(N''dbo.TrgUpdAudit_' + Upper (Rtrim (A.Name)) + ''') and OBJECTPROPERTY(id, N''IsTrigger'') = 1) Drop Trigger dbo.TrgUpdAudit_' + Upper (Rtrim (A.Name)) +
Char (13) + 'Go'
From sysobjects as A
Where A.Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Create Update Trigger' + '''' + Char (13) + 'Go'
Declare CurReadTbl Cursor Fast_Forward Read_Only For
Select Distinct A.Id,
Upper (Rtrim (SubString (A.Name, 1, 30)))
From sysobjects as A
Where A.Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Set @V_TblCtr = 0
Set @V_Sql = ''
Open CurReadTbl
Fetch Next From CurReadTbl
Into @V_TblId,
@V_TblName
While @@Fetch_Status = 0
Begin
Set @V_TblCtr = @V_TblCtr + 1
Set @V_ColCtr = 0
Set @V_Sql = 'Create Trigger dbo.TrgUpdAudit_'
Set @V_Fld = ''
Set @V_Sel = ' Select '
Declare CurReadCol Cursor Fast_Forward Read_Only For
Select Distinct A.ColId,
SubString (A.Name, 1, 35)
From syscolumns as A
Where A.Id = @V_TblId
And A.Name Not Like '%PICTURE%'
Order By 2, 1
Set @V_Sql = @V_Sql + @V_TblName + Char (13) + ' On dbo.' + @V_TblName + ' For Update As ' + Char (13) + ' Begin' + Char (13) + ' Insert Into ' + @V_TblName + '_AUDIT ('
Open CurReadCol
Fetch Next From CurReadCol
Into @V_ColId,
@V_ColName
While @@Fetch_Status = 0
Begin
Set @V_ColCtr = @V_ColCtr + 1
Set @V_Fld = @V_Fld + @V_ColName + ', '
Set @V_Sel = @V_Sel + 'Ins.' + @V_ColName + ', '
Fetch Next From CurReadCol
Into @V_ColId,
@V_ColName
End
Set @V_Fld = @V_Fld + Upper ('Posted_Action, Posted_By, Posted_Date')
Set @V_Sel = @V_Sel + '''' + 'UPDATE' + '''' + ', Host_Name (), GetDate () From inserted As Ins'
Set @V_Sql = @V_Sql + Rtrim (@V_Fld) + ')' + Char (13) + @V_Sel
Set @V_Sql = @V_Sql + Char (13) + ' End' + Char (13) + 'Go'
Print @V_Sql
Close CurReadCol
DeAllocate CurReadCol
Fetch Next From CurReadTbl
Into @V_TblId,
@V_TblName
End
Close CurReadTbl
DeAllocate CurReadTbl
Select 'Print ' + '''' + 'Drop Delete trigger' + '''' + Char (13) + 'Go'
Select 'if exists (select * from dbo.sysobjects where id = object_id(N''dbo.TrgDelAudit_' + Upper (Rtrim (A.Name)) + ''') and OBJECTPROPERTY(id, N''IsTrigger'') = 1) Drop Trigger dbo.TrgDelAudit_' + Upper (Rtrim (A.Name)) +
Char (13) + 'Go'
From sysobjects as A
Where A.Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Select 'Print ' + '''' + 'Create delete trigger' + '''' + Char (13) + 'Go'
Declare CurReadTbl Cursor Fast_Forward Read_Only For
Select Distinct A.Id,
Upper (Rtrim (SubString (A.Name, 1, 30)))
From sysobjects as A
Where A.Type Like 'U'
And (Upper (Name) Like (@v_Tablename)
And Upper (Name) Not Like ('%BACKUP%')
And Upper (Name) Not Like ('%_AUDIT%'))
Order By 1
Set @V_TblCtr = 0
Set @V_Sql = ''
Open CurReadTbl
Fetch Next From CurReadTbl
Into @V_TblId,
@V_TblName
While @@Fetch_Status = 0
Begin
Set @V_TblCtr = @V_TblCtr + 1
Set @V_ColCtr = 0
Set @V_Sql = 'Create Trigger dbo.TrgDelAudit_'
Set @V_Fld = ''
Set @V_Sel = ' Select '
Declare CurReadCol Cursor Fast_Forward Read_Only For
Select Distinct A.ColId,
SubString (A.Name, 1, 35)
From syscolumns as A
Where A.Id = @V_TblId
And A.Name Not Like '%PICTURE%'
Order By 2, 1
Set @V_Sql = @V_Sql + @V_TblName + Char (13) + ' On dbo.' + @V_TblName + ' For Delete As ' + Char (13) + ' Begin' + Char (13) + ' Insert Into ' + @V_TblName + '_AUDIT ('
Open CurReadCol
Fetch Next From CurReadCol
Into @V_ColId,
@V_ColName
While @@Fetch_Status = 0
Begin
Set @V_ColCtr = @V_ColCtr + 1
Set @V_Fld = @V_Fld + @V_ColName + ', '
Set @V_Sel = @V_Sel + 'Del.' + @V_ColName + ', '
Fetch Next From CurReadCol
Into @V_ColId,
@V_ColName
End
Set @V_Fld = @V_Fld + Upper ('Posted_Action, Posted_By, Posted_Date')
Set @V_Sel = @V_Sel + '''' + 'DELETE' + '''' + ', Host_Name (), GetDate () From deleted As Del'
Set @V_Sql = @V_Sql + Rtrim (@V_Fld) + ')' + Char (13) + @V_Sel
Set @V_Sql = @V_Sql + Char (13) + ' End' + Char (13) + 'Go'
Print @V_Sql
Close CurReadCol
DeAllocate CurReadCol
Fetch Next From CurReadTbl
Into @V_TblId,
@V_TblName
End
Close CurReadTbl
DeAllocate CurReadTbl
Set NoCount Off
Go