running a stored procedure inside a sql trigger
- by Ying
Hi all, the business logic in my application requires me to insert a row in table X when a row is inserted in table Y. Furthermore, it should only do it between specific times(which I have to query another table for). I have considered running a script every 5 minutes or so to check this, but I stumbled upon triggers and figured this might be a better way to do it.
But I find the syntax for procedures a little bewildering and I keep getting an error I have no idea how to fix. Here is where I start:
CREATE TRIGGER reservation_auto_reply
AFTER INSERT ON reservation
FOR EACH ROW
BEGIN
IF NEW.sent_type = 1 /* In-App */
THEN INSERT INTO `messagehistory` (`trip`, `fk`, `sent_time`, `status`, `message_type`, `message`)
VALUES (NEW.trip, NEW.psk, 'NOW()', 'submitted', 4, 'This is an automated reply to reservation');
END;
I get an error in the VALUES part of the statmenet but im not sure where. I still have to query the other table for the information I need, but I can't even get past this part. Any help is appreciated, including links to many examples..Thanks
