How should i keep track of the delete operations in database without using triggers?

Question

The appliocation polls the database after certain intervals of time. On each polling, the application would read all the tables.

As a part of optimization, we want that application should read the table only if any INSERT/UPDATE/DELETE has happened. So i want to use the timestamp concept.

Having a seperate timestamp column can help me in tracking any row modifications.

While querying on a table i can check if the in-memory stored timestamp is lesser than the max-of-TimeStamp in the table. if yes, it means that some row has been modified.

But if certain row gets deleted, then the latest timestamp assosiated with this row is no more pressent. Hence the above algorithm fails in this case since the max-of-timestamp does not give the correct value.

Is there a way in which i can track the delete operations as well without using triggers?

Any help would be highly appreciated. I am using Sybase ASA database.

Answer 1

Maybe you could implement a logical deletion. Instead of removing a record you simply mark it as deleted with a specific flag for example.

You still have the max timestamp and you can exclude the flagged records from the selection queries (maybe create some views on top of the table to do the job automatically).