How to create triggers in phpMyAdmin

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.

Note

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.

10 thoughts on “How to create triggers in phpMyAdmin

  • Hi.
    Wouldn’t it be better to create a view called student? Triggers are slow and have a lot of bugs which make them unreliable. At least one of these bugs probably affects you: if you have a foreign key which involves user and student tables, it sometimes stops the trigger: http://bugs.mysql.com/bug.php?id=11472

    • Hi Federico,

      Thank you for your comment.
      The truth is that I have never had any problem with that kind of trigger (involving foreign keys). Also, the bug that you report is in MySQL 5.0, and I am using MySQL 5.5 and I have never heard nothing about that bug, maybe they fixed it!

      Cheers!

  • I have this error but I don’t know what happend with these

    #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 ‘DELIMITER |
    CREATE TRIGGER time_spend AFTER INSERT ON papeletas
    BEGIN
    U’ at line 1

    These is the original code:

    DELIMITER |
    CREATE TRIGGER time_spend AFTER INSERT ON papeletas
    BEGIN
    UPDATE papeletas SET tiempo_pedid = TIMEDIFF(hora_reto,hora_sal);
    END
    |
    DELIMITER ;

  • i am creating trigger but showing this error
    plz help me
    #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 ‘END’ at line 6

    CREATE TRIGGER ai_pan_pet_trg
    AFTER INSERT ON pet
    FOR EACH ROW
    BEGIN
    INSERT INTO event (`name`) VALUES (new.name);
    END;

Leave a Reply

Your email address will not be published. Required fields are marked *