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 did this using HeidiSQL, but it should work in phpMyAdmin. 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

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.