History Table

Question

History Table

mbender

SSCrazy

Points: 2595
More actions
July 13, 2010 at 8:44 am

#223770

I plan on creating a sql trigger that before and after an update it inserts the data into a transaction history table. That way I have a before and after snap shot. The downside I see in doing this is if I ever add a new column to the original table I would have to remember to also add it to the transaction table. Is there a way to find out if all the columns of a table exist and if not it adds it then does the insert?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 1

You can find out the columns of a table from the INFORMATION_SCHEMA or sys.objects to determine if the source and history tables match.

It's probably a safer bet to just make the update of the history table + trigger part of the schema change deployment process instead of relying on a trigger to do so. The other problem you will run into is that your insert statement will have to be done using dynamic SQL so it will handle the additional columns based off of the INFORMATION_SCHEMA.

John

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

mbender (7/13/2010)
I plan on creating a sql trigger that before and after an update it inserts the data into a transaction history table. That way I have a before and after snap shot. The downside I see in doing this is if I ever add a new column to the original table I would have to remember to also add it to the transaction table. Is there a way to find out if all the columns of a table exist and if not it adds it then does the insert?

I don't know what your history table will look like but you have at least TRIPLED the amount of data storage you'll need for any tables you put into the history table. "Before" is the previous transaction's "After". So you have the equivalent of two BEFORE's and the AFTER.

I recommend you take a different tact unless you don't mind turn a 1GB table into (at least) a 3GB table with similar effects on backups and restores.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mbender SSCrazy Points: 2595 More actions · Answer 3

Thanks for the help, we are going to look at a different option or see if its truly needed.

Thanks

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Take a look at the following in your decision making process. It may help ...

http://en.wikipedia.org/wiki/Slowly_changing_dimension

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)