getting cached data from trigger, but not command line with perl

[Teddy Rodriguez]

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.

Code/commands:
Trigger1

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


Trigger2 - executes the perl script

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



PERL CODE:


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

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


$sth->execute();

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";
}