I am trying to write a change listener based on NodeJS or PHP. We have a huge database in MSSQL server. I want the change listener to be listening for a change in database for example a specific column changes in database and the change listener gets the information of that particular row that has been modified and than perform operations on it. I am not trying to make a real-time application for the users. I am just trying to log the changes of one local database in an remote database and I am require todo it in NodeJS or PHP. You can see the image i am posting below.
http://i.stack.imgur.com/c3jD6.png
The part That I totally Understand
I know that what I can do is make a request to database every x amount of time. I can use sockets or long polling etc. (NOTE: Please correct me if I am wrong)
The part that I dont understand
How I will be able to get only that particular row that has been modified through sql query? (NOTE: I can just get the data from that database, I am not able to modify anything in database). Database is so huge I will not be fetching all the rows again and again. I have not idea on this.
Note I am not doing any replication. What I am actually doing is monitoring a column in SQL server for changes and according to that change I perform certain operations in Remote database.
You cannot practically detect changes. Only SQL Server can track changes, and it exposes this tracking via Change Tracking or Change Data Capture. Your app, completely irrelevant of the access technology used (C#, C++, Node.js, ruby or whatever), can then interrogate the change tracking infrastructure and learn what rows have changed since last time it checked. Do not try to roll your own in-house developed change tracking mechanism, be it by triggers or 'changed_at' column or whatever. Many braves give up the ghost trying, save the headaches. Use the built-in off-the-shelf technologies for this task (linked above).
You say 'I am not able to modify anything in database' then the answer is simple: you are doing a fool's errand. Is impossible to detect changes w/o changing the database, is simple as that (comparison methods need not apply for any significant size). If they don't let you change the database (and enable one of the change tracking methods) then simply move on to a different project.
