Hi.
I realize this may not be the right place to ask, as it's a mysql trigger related question.. but there's some perl here too! Please see if you can spot something silly here, as I am running out of ideas with this issue here..
here's what's up:
it seems like the "after insert" trigger gets triggered before the values get inserted into the new row in table.
here's my trigger:
DELIMITER $$
CREATE TRIGGER `new_email` AFTER INSERT ON `dbmail_subjectfield`
FOR EACH ROW BEGIN
SET @exec_var = sys_exec(CONCAT('/bin/sh /home/vxp/veng/bin/deadweight
+ ', NEW.id));
END;
$$
DELIMITER ;
the "deadweight" is just that: a deadweight shell script that adds 20 (!) seconds to the execution via sleep, then executes a perl script that queries that new row, the one that triggered the trigger/deadweight, for values:
[vxp@vader bin]$ cat deadweight
#!/bin/sh
echo `date` >> /home/vxp/veng/deadweight.log
echo "Sleeping for 20 seconds" >> /home/vxp/veng/deadweight.log
sleep 20
echo `date` >> /home/vxp/veng/deadweight.log
echo "executing /home/vxp/veng/bin/email_retrieve.pl $1" >> /home/vxp/
+veng/deadweight.log
/home/vxp/veng/bin/email_retrieve.pl $1
[vxp@vader bin]$
the sequence of events is thus as follows:
1. a new row gets inserted, which triggers deadweight and passes the ID of the new record to it
2. deadweight sleeps for 20 seconds, and calls a perl script that queries that ID for values
3. perl script gets 0 rows.
if I query that new row manually (as in, not via the trigger), by just executing my perl script and passing it the new row's id, I get results, not 0 rows.
what's the deal here? how do I trigger my script AFTER the values have been successfully inserted into the new row?
Any help highly appreciated
