Image courtesy of Gualberto107 at FreeDigitalPhotos.net

I battled for ages to find the correct syntax to create a trigger in MySQL — the examples I could find were all too complex and I could not get any of them to work. Here is a quick little example that you should find really easy to follow. I completed this exercise using HeidiSQL, but it should work in phpMyAdmin.

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

https://dev.mysql.com/doc/refman/8.0/en/triggers.html

The idea behind the following TRIGGER is basically: when something happens in the first table (a new record is inserted for a new user) do something else automatically to the other table (a log entry is inserted for this activity).

Firstly, create two tables:

CREATE TABLE `tblUsers` ( `userID` int(11) NOT NULL auto_increment, `userName` varchar(100) NOT NULL, PRIMARY KEY (`userID`) );
CREATE TABLE `tblUsersLog` ( `userID` int(11) NOT NULL, `logDate` timestamp NOT NULL );

When a new User is added to the tblUsers table, the userID of that new User must be recorded in the tblUsersLog table along with the date on which the User was created.

  1. select the relevant database
  2. go to the SQL tab in phpMyAdmin, or the Query tab in HeidiSQL
  3. enter the following statement and click GO in phpMyAdmin or Run in HeidiSQL to execute the statement and create the TRIGGER
CREATE TRIGGER `lognewuser` AFTER INSERT ON `tblUsers` FOR EACH ROW INSERT INTO `tblUsersLog` (`userID`, `logDate`) VALUES (NEW.userID, NOW());

To test your TRIGGER, insert a new record into tblUsers:

INSERT INTO `tblUsers` (`userID`, `userName`) VALUES (NULL, 'Test User');

and check out your tblUsersLog: it should have one new record in it!

By foxbeefly

PHP / MySQL Developer. HTML, CSS and some JavaScript.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.