How to Create & Drop Trigger in MySQL

A trigger is associated with a database table and it is invoked when a particular event occurs for that table. A trigger is activated when a statement insert, update, or delete rows in the associated table, this is called the trigger events. A trigger can be activated either before or after the trigger events.

In this MySQL Trigger tutorial, you will learn how to create a trigger in MySQL. Also, we’ll show the MySQL statements to show and drop the created triggers.

For your better understand we’ll demonstrate trigger uses with a sample table called users. This table has id, first_name, last_name, and full_name columns.

Create Trigger

Now we’ll create a trigger for inserting full_name column value when a row is inserted in users table. full_name value would be the combination of first_name and last_name value.

CREATE TRIGGER before_user_insert BEFORE INSERT ON  users
FOR EACH ROW
SET NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name);

Run the above SQL in your database.

Run the insert query to the users table.

INSERT INTO users(first_name,last_name) VALUES('Codex','World');

Browse the users table, you’ll see the full_name column value is automatically inserted as per the first_name and last_name value.

Also, you need to update the full_name column value as per the first_name and last_name value when a row is updated. The below trigger statement helps you to do that.

CREATE TRIGGER before_user_update BEFORE UPDATE ON  users
FOR EACH ROW
SET NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name);

Run the above SQL in your database.

Run the update query to the users table.

UPDATE users SET first_name='Codexworld',last_name='Blog';

Browse the users table, you’ll see the full_name column value is automatically updated as per the updated value of first_name and last_name.

Show Triggers

The following statement lists the triggers defined in a database.

SHOW  TRIGGERS

Drop Trigger

The following statement drops a trigger.

DROP TRIGGER before_user_insert;