A trigger is a stored program invoked automatically in response to an event such as insert, update, or delete that occurs in the associated table.
MySQL supports triggers that are invoked in response to the INSERT, UPDATE or DELETE event.
Before Insert trigger : You can access and change the NEW values. However, you cannot access the OLD values because OLD values obviously do not exist.
After Insert trigger : You can access the NEW values but you cannot change them. Also, you cannot access the OLD values because there is no OLD on INSERT triggers.
Before Update trigger : You can update the NEW values but cannot update the OLD values.
After Update trigger : You can access OLD and NEW rows but cannot update them.
Before DELETE trigger : You can access the OLD row but cannot update it. Also, there is no NEW row in the BEFORE DELETE trigger.
After DELETE trigger : You can access the OLD row but cannot change it.
Let's create a MySQL AFTER INSERT trigger that is invoked automatically after a new row is inserted into a users table.
First, create a new table called users:
CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
mobile VARCHAR(12),
PRIMARY KEY (id)
);
create another table called messages that stores messages to users.
CREATE TABLE messages (
id INT AUTO_INCREMENT,
userId INT,
message VARCHAR(255) NOT NULL,
PRIMARY KEY (id , userId)
);
The following statement creates an AFTER INSERT trigger that inserts a message into the messages table if the mobile
number of the user is empty.
DELIMITER $$
CREATE TRIGGER after_users_insert
AFTER INSERT
ON users FOR EACH ROW
BEGIN
IF NEW.mobile='' THEN
INSERT INTO messages(userId, message)
VALUES(new.id,CONCAT('Hi ', NEW.name, ', Please update your mobile number.'));
END IF;
END
$$
DELIMITER ;
In the above statement,
after_members_insert is the name of the trigger.
AFTER INSERT is the triggering event.
ON users is the table that the trigger associated with.
FOR EACH ROW means trigger is activated for each row that is inserted.
Finally, inside the trigger body, insert a new row into the messages table if the mobile number of the user is empty.
Now let's test the trigger.
Insert two rows into the users table:
INSERT INTO users(name, mobile) VALUES
('a', ''),
('b','9584251586');
Then look into the users table.
Then check messages table.
I have inserted two rows into the users table. However, only the first row that has a mobile number empty, therefore, the trigger inserted only one row into the messages table.
To show the triggers in all database, use the following command.
show triggers;
To show the triggers in particular database, use the following command.
show triggers from databasename;
OR
show triggers in databasename;
To delete the trigger from the database, use following command.
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
