getting cached data from trigger, but not command line with perl
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";
}
Subject
Written By
Posted
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.