MySQL Forums
Forum List  »  Perl

getting cached data from trigger, but not command line with perl
Posted by: Teddy Rodriguez
Date: January 21, 2011 04:21PM

Hi all,

I have created a trigger that calls a perl script, which then emails me the new data that was put into the database. Everything works find EXCEPT the most important part - THE NEW DATA. When the perl script is called by the trigger it ALWAYS produces the data that was previously there (the trigger replaces the staging table where I do my query - it only has one row).

So for example, say the staging table has values 'test1' and '1234' respectively on 2 columns and new entries ('test2' and '5678') go into the table where I have a trigger - the trigger finds the new entry by NEW.entry and inserts/updates it to the staging directory. The email I get after the trigger is data 'test1' and '1234' and not the new data. BUT if run the perl script on the command line it works. ONLY WHEN EXECUTED BY THE TRIGGER where it does not work. I don't know if any of this is making sense, but if it does let me know if you have any ideas...thanks.


create trigger gettrig AFTER INSERT on test
for each row
delete from testtemp; ##this is the table I query in my perl
insert into testtemp (newhost, newip) values (new.hostname, new.ipaddress);

Trigger2 - executes the perl script

create trigger mailtrig AFTER INSERT on testtemp
for each row
set @result = ' ';
set @result = sys_exec('c:/windows/temp/email.bat');


$dbh->commit ();
my $sth = ' ';
my $ref = ' ';

my $sth = $dbh->prepare("SELECT * from testtemp");


while ($ref = $sth->fetchrow_hashref()) {
#print "Found a row: id = $ref->{'hostname'}, name = $ref->{'IPaddress'}\n";
$id = "$ref->{'newhost'}";
$ip = "$ref->{'newip'}";
print "$id\n";
print "$ip\n";

Options: ReplyQuote

Written By
getting cached data from trigger, but not command line with perl
January 21, 2011 04:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.