MySQL Forums
Forum List  »  Views

mysql after insert trigger
Posted by: Val Polyakov
Date: November 26, 2010 01:08PM

it seems like the "after insert" trigger gets triggered before the values get inserted into the new row in table.

here's my trigger:

--- cut here ---
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 ;

--- cut here ---

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:


--- cut here ---

[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]$

--- cut here ---

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

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql after insert trigger
6156
November 26, 2010 01:08PM


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.