www.devco.net by r.i.pienaar

Handling UPDATE errors in MySQL triggers

I've been doing a whole lot of programming recently and even getting into doing some MySQL stored procedure and trigger programming. I got a copy of the excellent book MySQL Stored Procedure Programming and can recommend it to anyone keen to get information on the subject.

Usually when dealing with errors in stored procedures or triggers you define a handler for the MySQL error code and either continue - and presumably do something to handle the exception - or exit with an error. When doing an UPDATE with a WHERE clause that does not match any data though no error gets thrown, it just doesn't do anything.

So I tried to come across some samples of how to get the affected row count but came up short - there are very few online resources that I found about MySQL stored procedures in general. So here is a solution for a simple trigger that updates a table when new data arrives in another.

DELIMITER $$
CREATE TRIGGER trg_update_latest_on_email_stats
AFTER INSERT ON email_stats
FOR each row
BEGIN
DECLARE l_rows INT DEFAULT 0;
UPDATE server_stats SET last_email_time = NEW.time
WHERE server_name = NEW.server_name;
/* how many rows did we affect? */
SELECT ROW_COUNT() INTO l_rows;
/* If we didn't update any rows, then insert new data */
IF (l_rows = 0) THEN
INSERT INTO server_stats (server_name, last_email_time)
VALUES (NEW.server_name, NEW.time);
END IF;
END $$

That's it, pretty simple stuff.

Data comes in, the trigger fires but if there is no data there nothing happens, so it inserts some data and future updates will pass.

I could have used the REPLACE function for simpler code, but my solution should be faster which is key when using trigggers.