Tracking history - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tracking history

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-02 : 09:36:14

I have HISTORY TABLE LIKE BELOW.

-- Table for tracking history
CREATE TABLE HISTORY
(
TABLE_NAME VARCHAR(100)
,FIELD_NAME VARCHAR(100)
,KEY_ID int --Primary key value of a row
,OLD_VALUE VARCHAR(100)
,NEW_VALUE VARCHAR(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:

status FLIGHTiD FLIGHTNAME FLIGHTEXTRANAME
old: 1 AAA SDSDSS
new: 1 AAAAAA SDSDSS

old: 2 BBB GGGG
new: 2 bbbbbb GGGG

Please help me how can i get this.

--Ranjit

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 09:59:11

Why you don't like to add timestamp column, and later just sort your data on this timestamp column

--------------------------
http://connectsql.blogspot.com/

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-02 : 10:06:04

In HISTORY table data will store tablename,filedname,oldvalue,newvalue
I am unable to join HISTORY table rows(i.e., tablename,filedname values) with actual TABLES and COLUMNS

I am new to timestamp column .

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources