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.
- 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
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!