Re: Perl to monitor a table in database

Something like this?

while (1) {
  my ($ct1) = $dbh->selectrow_array('select count(*) from TABLE');
  sleep 60;
  my ($ct2) = $dbh->selectrow_array('select count(*) from TABLE');
  print ($ct2 - $ct1)," rows added in the last minute\n";
}
[download]

Update: While I fully agree that a database trigger would be far more efficient and functional than a simple rowcount query, my suggestion was based on two assumptions:

The question was asked on PerlMonks, so presumably a Perl solution was the desired response.
The desire to monitor a database does not imply the desire or permission to alter it. Adding triggers or otherwise altering the database created and used by many commercial applications is a very bad idea: it can cause the application to break and technical support to be denied or limited to "remove your changes".

The original poster - an Initiate here - seems to have fully understood my suggestion, or rather the implications of it. I'm surprised, shocked even, at the non-Perl-based responses by people who have been here far longer.

Comment on Re: Perl to monitor a table in database Download Code

Replies are listed 'Best First'.
Re^2: Perl to monitor a table in database by MidLifeXis (Monsignor) on Oct 26, 2009 at 15:29 UTC
This will give you the difference in record counts, not the number of records added. For example: `t + 1: add 5 rows t + 10: add 5 rows t + 20: del 20 rows t + 30: add 5 rows t + 50: add 10 rows` [download] Your code would give an answer of 5 if $ct1 was obtained at time `t`. moritz's trigger answer would give a more accurate answer if you actually need the ~~could~~ count `[1]` of items added, and not just the difference in the row counts. Updates: Fixed spelling mistakes. --MidLifeXis	[reply] [d/l] [select]
Re^2: Perl to monitor a table in database by pid (Monk) on Oct 27, 2009 at 02:28 UTC
OK, fellows, (I thought there might be some modules available...forgive me), as I mentioned earlier, what I need now is a demo (or more exactly, a toy version), it's okay when it displays, so keszler's idea is applicable here. Later this grows to a larger one, I think I should use the triggers. Non-Perl-based ideas are welcomed here, too. :-p Once again, I'd like to thank everybody for your help, thank you.	[reply]