List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:August 6 2009 8:36am
Subject:Re: How to Detect MySql table update/difference
View as plain text  
Hi,

>Hi, I'm a novel developer of MySql and now I am trying to create a mysql 
>query to detect table updates.
>
>I query a database table every X seconds, and i want to get only the 
>different rows in the table. The result that I want to have is simply
>TABLE (t = now) - TABLE (t = X second ago)
>
>Every time that i query the database I store the table situation in another 
>table, called TABLE_TEMP, so the operation of difference >detection is
>TABLE_DIFERENCE  = TABLE - TABLE_TEMP.
>
>How can I do this query? The problem that I have is that I don't know the 
>structure of the table and I want to create a program with can be >used for 
>all types of data and tables.

Although in relational theory, a "relation" means the table or view and you
should be able to modify one relation to another, in SQL databases, it
doesn't work that way.

In SQL databases, a "table" doesn't get modified, you modify rows. A
table is nothing but a description of a certain structure. So in order to 
know
if any row in a table has been modified, you either need to have some kind
of timestamp in each row or keep a copy of the table since it's last 
modification
check.

Instead of querying the table, I guess you could write a trigger that 
updates
a "metatable" that says a row has been added/deleted/modified for a
particular table.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com 

Thread
How to Detect MySql table update/differenceBS TLC5 Aug
  • RE: How to Detect MySql table update/differenceRolando Edwards5 Aug
    • Re: How to Detect MySql table update/differenceBS TLC5 Aug
      • RE: How to Detect MySql table update/differenceDaevid Vincent6 Aug
  • Re: How to Detect MySql table update/differenceMartijn Tonies6 Aug