MySQL & trigger before insert

Question

I need to create a trigger in my database. The trigger must be able to analyse event before insert. In case of the last event already existing in the database has the same status, the trigger has to cancel the insert and update the last event.

I tried to create one but I can't add it in my database because of mysql errors:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 

Here the trigger:

CREATE TRIGGER events_before_insert
BEFORE INSERT ON monitoringV2.events
FOR EACH ROW
BEGIN
DECLARE id_last_event;
DECLARE status_last_event;

SELECT id INTO id_last_event, status INTO status_last_event FROM events WHERE module_id=NEW.module_id ORDER BY serverDate DESC LIMIT 1;

IF NEW.status = status_last_event THEN
UPDATE events SET serverDate=NOW() WHERE id=id_last_event;
signal sqlstate '00000' set message_text = "Update instead of insert";
END IF;
END;

Here are information server:

PHP 5.4.6 VC9
Apache 2.4.2 VC9
MySQL 5.5.27
PhpMyAdmin 3.5.2.2

Thanks for your help

Regards,

Dorine M.

Answer 1

You have to change DELIMITER before creating a trigger like this

DELIMITER $$
CREATE TRIGGER... 
...
END;
$$
DELIMITER ;

Answer 2

You might not need to implement a trigger for this. The operation you are describing is formally known as UPSERT, i.e. update or insert.

This is how it's done in MySQL and you could also look at REPLACE.

Here is another question covering this.

Answer 3

You will need to delimit your create statement:

delimiter //
CREATE...
...
END;
//
DELIMITER ;

and you will probably need to define a type for each variable declared, e.g.

DECLARE x INT;