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<\/i> and can recommend it to anyone keen to get information on the subject.<\/p>\n
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.<\/p>\n
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.<\/p>\n
\n\nDELIMITER $$\nCREATE TRIGGER trg_update_latest_on_email_stats\nAFTER INSERT ON email_stats\nFOR each row\nBEGIN\nDECLARE l_rows INT DEFAULT 0;\nUPDATE server_stats SET last_email_time = NEW.time\nWHERE server_name = NEW.server_name;\n\/* how many rows did we affect? *\/\nSELECT ROW_COUNT() INTO l_rows;\n\/* If we didn't update any rows, then insert new data *\/\nIF (l_rows = 0) THEN\nINSERT INTO server_stats (server_name, last_email_time)\nVALUES (NEW.server_name, NEW.time);\nEND IF;\nEND $$\n<\/pre>\n<\/blockquote>\nThat’s it, pretty simple stuff.<\/p>\n
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.<\/p>\n