Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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

 

Posted on Thursday, March 15, 2007 10:47 AM SQL Server 2000 Tricks | Back to top


Comments on this post: create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID

# re: create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID
Requesting Gravatar...
That's a lot of code for the front page! Maybe next time you make a large post, you would be nice enough to make it an article :)
Left by Tim Hibbard on Mar 15, 2007 9:40 PM

# re: create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID
Requesting Gravatar...
It's remarkable to see such clever posts here. Your outstanding essay is a delight for people of all preferences. And it certainly has an advantage over similar sites.
Left by larry on Jun 25, 2010 10:17 AM

# re: create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID
Requesting Gravatar...
What a brilliant post, thanks so much for sharing this with us.
Left by canvas art prints on Dec 09, 2011 9:00 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net