To watch SQL column which has been changed recentely

Question

I need to watch sql columns in table, which has been recently changed with out using last modified date

Could you help me to sql query for latest changes.

original table:

 S.No Employee id       First Name   Last Name   Address 1
  1    00007              J           Siva        East st
  2    020007             G           Moorthy     West street
  3    300007             gan         thanu       East st
  4    100007             esh         Moor        West street

Modified table:

S.No Employee id       First Name   Last Name   Address 1
 1    00007               Jun        Siva      East st
 2    020007              Gon        Moorthy   West street
 3    300007              gan        thanu     East st
 4    100007              esh        Moor      West street

I need to fetch only below 2 rows:

S.No Employee id       First Name   Last Name   Address 1
1    00007              Jun          Siva      East st
2    020007             Gon          Moorthy   West street

Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Apr 18 '18 at 07:41
Most of the commercial databases offer built in features to track changes ("audit"). The solution to your problem depends completely on the database product you are using (PostgreSQL, Oracle, DB2, Microsoft SQL Server, ...). As long as you don't tell us that, this is impossible to answer. (And no, "SQL" is not the name of a database product, it's a standard for a query language. Every relational database is a "SQL database") — , Apr 18 '18 at 07:57

DineshDB · Answer 1 · 2018-04-18T07:49:50.013

0

Solution 1:

You have to ADD a column in the table like Modified_Date, and update the column with GETDATE() on every update.

Every time the column updates, and it is easy to find the recently updated columns.

S.No Employee id       First Name   Last Name  Address 1    Modified_Date
 1    00007               Jun        Siva      East st      2018-04-18
 2    020007              Gon        Moorthy   West street  2018-04-18
 3    300007              gan        thanu     East st      NULL
 4    100007              esh        Moor      West street  NULL

And you can get the recently updated values using:

SELECT * 
FROM Your_Table 
WHERE Modified_Date IS NOT NULL
ORDER BY Modified_Date DESC

Solution 2:

Otherwise you have to create TRIGGERS to track the updates.

edited Apr 18 '18 at 07:49

answered Apr 18 '18 at 07:44

DineshDB

5,998
7
33
49

@VinodhMuthusamy, Then you have to create `TRIGGERS` and track the data. – DineshDB Apr 18 '18 at 07:53
with out using trigger, we need to implement. – Vinodh Muthusamy Apr 18 '18 at 09:35

score 0 · Answer 2 · answered Apr 18 '18 at 07:48

SQL alone cannot "watch" for changes in data. It's just a query language that you decide when (and whether) to call.

There are, however, DBMS-specific mechanisms that can help you watch for data changes without the need to poll (periodically re-query). For example, SQL Server has SqlDependency.

Or you can implement triggers that send notifications to the client application, which may come with its own set of trade-offs.

score 0 · Answer 3 · answered Apr 18 '18 at 11:25

It appears that you are unable to modify the schema of the source database for whatever reason as the previous answers relating to modified date and triggers should be sufficient. Alternatively you could poll the data into a separate audit table and perform comparisons periodically using a WHERE clause and checksum. However you may miss out on changes as the polling mechanism would not be triggered by a change in the data and values could change more than once in the polling interval.

To watch SQL column which has been changed recentely

3 Answers3