April 23, 2008 at 6:48 am
I've inherited a database (and a DBA) with a few "issues".
Each table in the database has a matching table to capture the audit trail. When an insert or update (we never delete) occurs to a row, a trigger copies the original record into the audit table.
The DBA stuck three additional fields to the end of each audit table to catch the details of the updating user, datetime stamp and an indicator as to whether it was an update or insert.
My problem is that I now need to add some new columns to both the main table and, therefore, the audit table using a SQL script with the minimum amount of fuss but because the trigger doesn't match the columns by the column names, just the index, it falls over unless the newly added columns come before the three random columns used by the DBA to capture the timestamp etc.
Confused? Me to...
So I have TableA with column1, column2, column3
I then have TableA_History with column1, column2, column3, username, timestamp, eventtype.
I want to add column4 to both tables using a pretty simple script. It's easy to fix TableA with
ALTER TABLE dbo.TableA ADD
Column4 int NULL
GO
But I can't use the same for TableA_History as it needs to come directly after column3 and before the username, timestamp, eventtype columns.
I don't want to have to hand craft a separate statement for each of my 25 history tables if I can help it. Is there any simpler way to generically insert a column into the middle of a table?
TIA
LMS
April 23, 2008 at 7:31 am
Wouldn't it be easier to modify the triggers? They seem to smell from what you are saying.
Piotr
...and your only reply is slàinte mhath
April 23, 2008 at 7:36 am
Probably, but I'm not allowed to do that, my DBA generally calls the shots and basically said that he's going to make all the changes to the audit tables by hand so I was just wondering if there was a scripty way I could do it so I can send him the script, say "Just run this, there's a good boy" and the world will be a happier place 😉
April 23, 2008 at 7:50 am
If you have to rearrange the columns on the second table, the best way is to drop & recreate the table. Or will that cause issues somewhere else?
Use the MS method, rename the old table, create the new one with the columns in the right order, move the data from the old table to the new, drop the old table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2008 at 8:08 am
Grant is there a way to script that rename/drop/create generically rather than having to do a separate script for each of my 25 tables?
April 23, 2008 at 8:21 am
Nope. Sorry.
The best you can do is write some code to generate the scripts, but you'll need 25 commands for 25 tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2008 at 8:33 am
Bah humbug 😛
Thanks though!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply