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.
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.
- select the relevant database
- go to the SQL tab in phpMyAdmin, or the Query tab in HeidiSQL
- 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
INSERT INTO `tblUsers` (`userID`, `userName`) VALUES (NULL, 'Test User');
and check out your
tblUsersLog! It should have one new record in it.