It came to my attention this week that a specific UPDATE statement via a web application was causing absolute chaos with another component in the system. There were two answers with regards to how to fix this:
1) Wait for the web developer to find time to patch the web application, though this could cause a user with access to the database to make the same error.
2) Create a BEFORE trigger to prevent the change from happening both through the application and via a database client
Even for those proficient in SQL triggers can be a bit tricky in MySQL so having a base for comparison to other queries can be a useful jumping point.
I found it quite easy to find examples of triggers that prevented rows from being deleted or inserted but finding an example of an UPDATE trigger that was not convoluted and stopped an update in an intuitive manner was more difficult. Where simplicity can be obtained a complexity causes unnecessary problems. The specific situation I was dealing with was a specific ID being used twice in a single row. An id could not be its own parent and when that happened it caused issues. It was not something that made sense to do when you understood the connection between the two fields but it still happened.
Below is trigger preventing that from happening – it does the job in both 5.5 and 5.6. Let us say that our table is called test:
DELIMITER ;;
CREATE TRIGGER parentid_cannot_match_id
BEFORE UPDATE on test_table
FOR EACH ROW
BEGIN
IF (NEW.parentid = OLD.id)
THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘An id cannot be its own Parent’;
END IF;
END;;
DELIMITER ;
I am going to break this down so that it can be edited to another similar specification. Note that triggers work on a per database level so if you have multiple databases on the same server make sure you are using the correct database at the time via the USE database statement. If our database is testdb the statement would be:
mysql>Use testdb;
Now the statement:
*DELIMITER ;;
The delimiter is necessary so that despite the semicolons, which usually act as delimiter in MySQL, being used within the trigger it doesn’t cause the query to be run just until the point of the next semicolon. It is a separate query essentially but necessary and is run at the same time as the create trigger statement.
*CREATE TRIGGER parentid_cannot_match_id
This is the start of the create a trigger statement itself and then states the name of a trigger. It cannot match the name of another trigger nor be similar to another trigger being run on the same table.
*BEFORE UPDATE on test_table
This states that this is a BEFORE trigger NOT an AFTER trigger. An after trigger would be useless at this point because the action on the table would have already happened, defeating the purpose of preventing said action. Our table name is test_table so it is saying before any update on test_table.
*FOR EACH ROW
This row states that following this phrase is the body of the trigger which is to be performed on each row being affected by the trigger.
*BEGIN
The rows before this keyword are necessary parts of any trigger. When we see statement that begins with BEGIN followed by END multiple arguments are allowed in the middle for these two keywords which form a compound statement construct. As we are working an IF (.. THEN) statement it is considered a compound statement even though we are only concerned with a single argument. This can also be used to use for multiple arguments as you cannot have similar triggers on the same table.
*IF (NEW.parentid = OLD.id)
This is the parameter I am looking for that I find to be unacceptable. The NEW and OLD keywords are used within triggers to differentiate between the NEW (post update values) and OLD (pre-update values). Both the NEW and OLD keywords were necessary for this trigger to target the single line I was interested in. Without the OLD and NEW keywords or just using the NEW keyword no row’s ‘parentid’ could be updated with an already existing id value which was problematic. If you were running a regular SQL query for information ‘parentid=id’ would be a valid way of stating that you want results where ‘parentid’ is equal to ‘id’ but in triggers it does not work the same way. After the OLD keyword was tagged on to ‘id’ it specified I was referring to that specific row pre-update and post update.
*THEN
If the IF statement finds a positive result, do the following next.
*SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘An id cannot be its own Parent’;
The SIGNAL keyword will cause MySQL to return and error which will be whatever SQLSTATE I specify (more on that here). 45000 is a generic error which states that this error is specific to a user condition, which is essentially what we are doing by saying send this error if it meets the criteria in the IF statement. As it is a user defined error we should specify what is causing this error. This error will show up if you are running the disallowed query from commandline or a MySQL client. The application, in my case the web application, will not necessarily show you the error specified unless the code tells it to do so. It may have a default for any error.
*END IF;
Here we end the IF (THAN) statement.
*END;;
Here we end the BEGIN (END) statement.
*DELIMITER ;
Then we change the delimiter back to a single semicolon. Note this is a separate query from the trigger so if you are running this via commandline you will have to press enter again if you do not include a line break after the statement.
For more information on triggers see the MySQL documentation which is very readable for this specific topic.
HA Lessons Learned 