May 2, 2011 at 7:55 am
I have HISTORY TABLE LIKE BELOW.
-- Table for tracking history
CREATE TABLE HISTORY
(
TABLE_NAMEVARCHAR(100)
,FIELD_NAMEVARCHAR(100)
,KEY_ID int --Primary key value of a row
,OLD_VALUEVARCHAR(100)
,NEW_VALUEVARCHAR(100)
)
data inserted into this table by frontEnd application when particulor table updated.
and also have 50+ other tables
for example consider sample table
CREATE TABLE SAMPLE
(
FLIGHTiD INT
,FLIGHTNAME VARCHAR(100)
,FLIGHTEXTRANAME VARCHAR(100)
,FLIGHTDATE DATETIME
)
INSERT INTO SAMPLE VALUES(1,'AAA','SDSDSS',GETDATE())
INSERT INTO SAMPLE VALUES(2,'BBB','GGGG',GETDATE())
INSERT INTO SAMPLE VALUES(3,'CCC','BBBB',GETDATE())
INSERT INTO SAMPLE VALUES(4,'DDD','TTTT',GETDATE())
--=======================
NOW using front end application data inserted into HISTORY table when particular field of a particular table updates
assume history table having data like below
insert into HISTORY values ('SAMPLE','FLIGHTNAME',1,'AAA','AAAAAA')
insert into HISTORY values ('SAMPLE','FLIGHTNAME',2,'BBB','bbbbbb')
Output:
when i run a query it should show following output:
statusFLIGHTiDFLIGHTNAMEFLIGHTEXTRANAME
old:1AAA SDSDSS
new:1AAAAAA SDSDSS
old: 2 BBB GGGG
new: 2 bbbbbb GGGG
Please help me how can i get this.
--Ranjit
May 2, 2011 at 11:04 am
I see a couple of big hurdles for what you are trying to do. First an audit or history is about a million times easier if you record the whole record instead of changes to each field. The next big challenge is there is now way to know which record in the history table you want to get. There is no date field to get you a proper order by. The other challenge is the structure looks like you are going to have multiple fields updated and you want to get the most recent values for each column? Do you see where this going and why a copy of the whole record is a lot easier?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 9:52 pm
I agree with sean. why don't you look for triggers to do the same for you.
I know trigger might delay your execution a bit because actually there will be at least 2 write per transaction on the object . but keeping in mind your requirement better you create DML Triggers to maintain history and also as sean highlighted it is very important to maintain key column which could help you relate the historytable with your original table.
Regards,
May 2, 2011 at 10:45 pm
Hi Sean Lange,
for getting the most recent values for each column
i may use row_number() with partition by table name,column name
Please help me how can join rows of a history Table with original table columns.
database having 50+ tables and 100+ views
i need to show all tables ,views with old value as one record
and new values as another record
May 2, 2011 at 11:11 pm
Hi sachnam,
DML Triggers not needed for my scenarios.
records will store in HISTORY table by front end application.
My requirement is :
at first ATP_FA_FLIGHTS table having following data
FLIGHT_IDFLIGHT_NAMEFLIGHT_EXTRANAME
151old flightold extra name
in the above table user can update by using front end application
when ever user trying to update records the new value, old value of a column will store in ATP_TRANSACTION_HISTORY table
consider ATP_TRANSACTION_HISTORY table having following data
TABLE_NAMEHISTORY_TABLE_KEY_IDHISTORY_FIELD_NAMEHISTORY_OLD_VALUEHISTORY_CURRENT_VALUE
ATP_FA_FLIGHTS151FLIGHT_NAMErewnownew flight
ATP_FA_FLIGHTS151FLIGHT_EXTRANAMEewrewnew extra name
when ever updation completed data will update in ATP_FA_FLIGHTS
now ATP_FA_FLIGHTS table having following data
FLIGHT_IDFLIGHT_NAMEFLIGHT_EXTRANAME
151new flightnew extra name
like wise user can update any record from any table
data base having 50+ table , 2000+ columns
OUTPUT:
when i run a Query I need get following results
STATUSFLIGHT_IDFLIGHT_NAMEFLIGHT_EXTRANAME
old151old flightold extra name
new151new flightnew extra name
Please help me in how to form a query.
Thanks
--Ranjit
May 3, 2011 at 7:09 am
IRK (5/2/2011)
Hi Sean Lange,for getting the most recent values for each column
i may use row_number() with partition by table name,column name
Please help me how can join rows of a history Table with original table columns.
database having 50+ tables and 100+ views
i need to show all tables ,views with old value as one record
and new values as another record
Like I said if you want to have a full history of each record you are going to have a nightmare to maintain queries when the history of each column is in a history table. You will have to join to this table for each column to get the most recent value for each column. In my opinion this is not a good approach and trying to help with the queries is way beyond the scope of a forum. It would take a few days to write something that complex and it seems totally unnecessary when just recording the whole record makes it so simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2011 at 7:16 am
Thank you Sean Lange,
Now I am adding HISTORY_TRANSACTION_DATE column to
ATP_TRANSACTION_HISTORY table
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply