Triggers are code that we associate with a table to be executed when certain actions occur, for example, after a new INSERT statement in the book table. The action does not need to happen within phpMyAdmin.
Contrary to routines that are related to an entire database and are visible on the database's Structure page, triggers for each table are accessed from this specific table's Structure page.
Note
Prior to MySQL 5.1.6, we needed the SUPER privilege to create and delete triggers. In version 5.1.6, a TRIGGER table-level privilege was added to the privilege system. Hence, a user no longer needs the powerful SUPER privilege for these tasks.
In order to perform the following exercise, we will need a new INT column—total_page_count—in our author table.
The idea here is that every time a book is created, its page count will be added to the total page count of the books from this author. Some people may advocate that it would be better not to keep a separate column for the total here, and instead compute the total every time we need it. In fact, a design decision must be made when dealing with this situation in the real world. Do we need to retrieve the total page count very quickly, for example, for web purposes? what is the response time to compute this value from a production table with thousands of rows? Anyway, since I need it as an example, the design decision is easy to make here.
Let us not forget that following its addition to the table's structure, the total_page_count column should initially be seeded with the correct total. (However, this is not the purpose of our trigger.)
The current phpMyAdmin version does not have an interface for trigger creation. Therefore, we enter the trigger definition in a query box taking special care to enter // in the delimiter box:
CREATE TRIGGER after_book_insert AFTER INSERT ON book FOR EACH ROW BEGIN UPDATE author SET total_page_count = total_page_count + NEW.page_count WHERE id = NEW.author_id; END //
Later, the Structure page for our book table reveals a new Triggers section that can be used the same way as routines, to edit or delete a trigger, as shown in the following screenshot:
Contrary to testing stored procedures or functions, there is neither a CALL sequence nor a function inside a SELECT statement to execute the trigger. Any time the defined operation (a book INSERT) happens, the code will execute (in our case, after the insertion). Therefore, we simply have to insert a new book to see that the author.total_page_count column is updated.
Of course, a completely automatic management of this column would involve creating AFTER UPDATE and AFTER DELETE triggers on the book table.