Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
paul27uk
Starting Member
9 Posts |
 Posted - 2007-08-10 : 07:00:08
|
| Hi, hoping I can get a few view on a question I have relating to the above.I am new to Stored Procedures and Triggers and I am trying to understand 'best practice' a little better. Here is my question: If I have a table that stores information, and when any field in that table is updated (and changes) I would like to inactive the row, prior to change and then add the change by way of a new, active row. This way I can see what it was before and that it's inactive, and what the active value is.Hope this makes sense, if this is the wrong way to manage change history any suggestions would be appreciated.A second question I have is as follows: If I have a table that stores a number, based on that number, what would be the best way to create new records in a different table that pulls from the first table. Where the number stored in table 1 represents how many times the record is to be created in the second table.Thanks. If anyone needs more data, please feel free to ask, I will help as best as I can and appreciate any advice & comments that you can give.Paul |
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-10 : 07:48:17
|
| You want an audit trail. Google for "sql server audit trigger" and you will get loads of examples. The key difference is the old data is written to an audit table and the new data remains in your table so you have current and historical tables. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-10 : 07:53:04
|
| You can do it like that but what are you trying to accomplish?If the application needs the current value and you just want to be able to track changes then it's best to hold the old data on an audit table - it'll save the current rows table getting big (you could use a partitioned table for this partitioning on the current row flag but that seems a bit excessive).A trigger would be a reasonable way to maintain this table. If performance is an issue then replicate to another database and do the audit trail processing there.Join to a numbers table to get the row entriesinsert newtblselect t.*from oldtbl tjoin numbers non t.num >= n.numYou can use a cte to create the numbers table in the query or instantiate it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
paul27uk
Starting Member
9 Posts |
Posted - 2007-08-13 : 04:55:19
|
| What I am trying to do is a few things. Firstly I want to have a trigger(s) so that if certain fields are updated on a table, it records the tablename, the primary key, the column name, oldvalue and new value, and who & when the change was made into a ChangeHistory table. Another one I want to achieve is more complicated. I have 2 books on T-SQL/SP&Triggers It just doesn't seem to be quite sinking in at the moment as I have no real experience of this type of thing, very keen to learn this though so any advise is very much appreciated. :O) |
|
|
|
|
|
|
|
|
Best practise - Advice needed on SP/Triggers)