DELIMITER $$
CREATE TRIGGER messages_ins_recipientuser BEFORE INSERT ON messages
FOR EACH ROW
BEGIN
SET NEW.recipient_user_id := (SELECT users.id FROM users WHERE users.mobile=NEW.recipient_mobile ORDER BY id ASC LIMIT 1);
END;
$$
delimiter ;

Run this in the MySQL prompt; MySQL Administrator (MySQL Query Browser) won’t take it.

To see all the database’s triggers:

SHOW TRIGGERS;

Other useful hints:

  • Auto-incremented primary keys aren’t set in BEFORE INSERT triggers. It is possible to get the ID this way: (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table_name')