running a stored procedure inside a sql trigger

Question

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

Answer 1

I don't know what you've defined for the data type of reservation.sent_time, but I notice you're putting the NOW() function in quotes, making it a literal string 'NOW()' instead of a function call to get the current time.

---

Okay, here are two other issues:

First, the IF statement in the MySQL trigger language requires an END IF; after the block.

Second, are you using DELIMITER? For example:

DELIMITER //

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 IF;
END//

DELIMITER ;

This is needed because the mysql client allows you to run multiple statements in interactive mode or in an SQL script. But it's kind of dumb -- it treats the input as one SQL statement until it gets to the terminator of the statement. Normally the terminator is the semicolon (;).

But there are literal semicolon inside your trigger body! If the mysql client stops at the first semicolon, it will try to define your trigger using an incomplete statement.

The solution in the mysql client is to temporarily change the statement terminator to some other character or sequence of characters that doesn't appear in your trigger body. For example: //.

(Note that the DELIMITER command is a built-in of the mysql client. It's not recognized if you are executing dynamic SQL statements from your application.)