0

Hello Everyone, I am building up a webpage using asp.net which will populate data from a table@ my sql database. I need to copy data from one table to another for history purpose every 5 secs. My question: how can I implement a sql script which will run continuously on server, check and insert data from one table to another so that my webpage will refresh latest data every 5 secs? Also, is it possible to implement using asp.net only even if webpage is not opened?

This is my 1st web project so please bear with me. Responses are highly appreciated!

user2801184
  • 329
  • 7
  • 27
  • you can use trigger or schedule a job for sql query to run. – Sain Pradeep Oct 16 '13 at 06:09
  • Why not display the data directly from the original table, without copying it to the second one? Data duplication is really something to avoid whenever possible (and I'm not seeing a compelling reason based on the description here). – musical_coder Oct 16 '13 at 06:17

2 Answers2

0

You can go through this link to schedule an event Event Scheduler

Some useful links
mySQL daily backup from one table to another

Edit -1

But the best will be using trigger for your table. Example

MySQL trigger On Insert/Update events

Community
  • 1
  • 1
शेखर
  • 17,412
  • 13
  • 61
  • 117
0

Here are one of the few things u can do:

  1. Create a VIEW of the original table in the database you want the data to be accessible.
  2. Create a TRIGGER as Shekhar has suggested.
  3. Write a cron job which picks values from one table and inserts it in another. U can do this on the basis of creation_time/updation_time and you will need to handle duplication.

Solution 1&2 will work only when both the databases are on same mysql server while 3 will work otherwise as well.

azi
  • 929
  • 1
  • 11
  • 31
  • I wrote a trigger but it is only validating for one cell of the column instead of entire column. My question: If value of the column changes to particular value eg: Status= 'Start' Or Status= Pause how can I write a trigger which will copy entire row to another table? Please shar the example if possible. You guys have nbeen kind. Thank you so much! Can I mark multiple answers correct? – user2801184 Oct 16 '13 at 23:17
  • can you please post the trigger definition for us to see... to handle updates you can use `after update` triggers... definition would somewhat look like `CREATE TRIGGER after_table_update AFTER UPDATE ON table_test FOR EACH ROW target_table_test SET a=NEW.a, b=NEW.b...`.. but you will have to different multiple trigger definitions for insert and update... while if u use views, one definition would suffice. – azi Oct 17 '13 at 06:05