Some time ago I needed to create a trigger in my MySQL database and I tried to do it through phpMyAdmin. If you have tried it some time, probably, you have already realised that it is a “complicated” task. I say complicated because phpMyAdmin does not allow you to use the standard MySQL sintaxis to create triggers in your database. So, today I will show you a way to introduce triggers in your MySQL database using phpMyAdmin for that.
Creating triggers in MySQL
In order to create a trigger in MySQL we can follow the recommendations that we can found in http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html (or http://dev.mysql.com/doc/refman/5.0/es/create-trigger.html for MySQL 5.0). There, they explain us perfectly how we should create our trigger and they even provide some examples. In case you do not want to visit that website or you do not have the time for doing it, here I put an example of creating a trigger. In this example we assume that we have a users table called “user” and a table of students, which are a subset of users, called “student”, so it is necessary to insert the user id in the table of student immediately after it is inserted in the user table:
CREATE TRIGGER ins_student AFTER INSERT ON user FOR EACH ROW BEGIN IF NEW.type = 'student' THEN INSERT INTO student (`id`) VALUES (NEW.id); END IF; END;
Well, let’s explain a little this code. In the first line we create the trigger called “ins_student”, which is ALWAYS triggered every time that a new user is inserted in the table “user”. The next line indicates that this trigger will fire on each insertion of a new row. Then we create an IF block which asserts if the type of the new record is equal to the literal ‘student’, then the new id is inserted in the “student” table. Finally we close all the blocks.
Creating the trigger through phpMyAdmin
If we try to put this code in a SQL statement in phpMyAdmin and run it as it is, it will give us some problems with separators from phpMyAdmin itself. I mean, phpMyAdmin uses the “;” as the default query separator, and if you look at the sentence, we have used that character as instruction separator because it is required so by the MySQL server. So, here it is where comes the most IMPORTANT part of this article and which more headaches gave me, and it is something as simple as changing the phpMyAdmin’s delimiter in the text box that is located just below the text box where you enter the SQL query. It’s as easy as changing the value of that text box (which is now “;”) by the value “//”. Now we can run our sentence without problems, since the MySQL server now understands that the “;” is not SQL queries delimiter, but it is the character for end of instruction. With this we have our trigger up and running in our MySQL database through phpMyAdmin.
If your phpMyAdmin SQL editor does not allow you to edit the DELIMITER field or simply you do not see that text field anywhere, it is because your version of phpMyAdmin is very old and does not support the modification of the DELIMITER. So, you also can not modify it using a MySQL query, since the DELIMITER statement is not part of the set of statements accepted by the MySQL server, but part of the MySQL client used to connect to the server.