MySQL Trigger Tutorial With Examples

Learn about MySQL Trigger in this hands-on tutorial with examples. Explains how to use CREATE, INSERT, DELETE, and UPDATE Triggers in MySQL:

MySQL TRIGGER is an object associated with a table in MySQL. It’s an action that’s executed in response to a defined event on the table.

For example, You can specify a trigger to get executed when an INSERT, UPDATE or DELETE operation happens in a table.

It should be noted that the main requirement for executing triggers is the SUPERUSER or root privilege from the account where triggers are being executed.

=> Check ALL MySQL Tutorials Here

MySQL Triggers

In general, there are 2 types of Triggers

• Row Level Triggers: These types of triggers are activated or called for each insert/update/delete of every single row.
• Statement Level Triggers: The statement-level triggers are called for each execution of a statement. A statement might impact 1 or more rows but associated triggers would be called only once. These types of triggers are supported by SQL databases like Oracle, PL/SQL, etc.

MySQL 8.0 supports just row-level triggers which are called for each insert/update/delete of the row.

Syntax

Let’s look at the syntax to define or create a TRIGGER in MySQL.

CREATE TRIGGER 
trigger_name 
trigger_time 
trigger_event
ON table_name FOR EACH ROW
[trigger_order]
body

trigger_name: A unique name for the trigger object within a table

trigger_time: Allowed values are BEFORE & AFTER – This field indicates whether trigger would be activated BEFORE or AFTER the event.

trigger_event: Is the actual event or action that would lead to invoking the trigger.

The allowed values for this field are:

• INSERT: For every row insert.
• UPDATE: For every row modification i.e. row updates using UPDATE statements.
• DELETE: When a row is deleted from the table.

table_name: The name of the MySQL table for which the trigger is being defined.

trigger_order: This is an optional field and is used to define the order in which the trigger would be executed. This is generally used when there are multiple triggers associated with the same events – In that case, we can define the execution order.

It uses the below syntax:
{FOLLOWS | PRECEDES} other_trigger

Note: If the trigger_body is going to contain multiple statements, we can use the syntax similar to how we use it for creating a STORED PROCEDURE.

The trigger body, in that case, would be enclosed between BEGIN and END commands and the entire Trigger definition would be between the DELIMITER commands to have multiple statements specified with BEGIN…END blocks.

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Applications of Using MySQL Triggers

There are numerous real-life applications of MySQL triggers.

• Checking for data Integrity / Error handling: This is done especially by using BEFORE triggers – suppose you want to check whether the inserted column value follows a proper syntax or it’s within a certain range etc.
• Creating change log entries: The triggers are widely used to create audit trails or changelogs of activity in a database or table. For example, if you want to track all updates to a table, then we can create an UPDATE trigger on that table which would create another entry in an audit table.
• Derive / Update additional data field: At times it might be required to update one or more columns in response to the original UPDATE or INSERT. Triggers can be useful in those situations.
• Replicate data across different tables: At times, we might want to insert / update data to other tables as a result of a data insertion or update on another table. In such cases, we can define triggers to achieve the desired table data updates.

Test Data

Table name – item_inventory
Attributes – name – VARCHAR(30), price – DECIMAL(5,2), quantity INTEGER
Table Creation Script

CREATE TABLE item_inventory (id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(30), price DECIMAL(5,2), quantity INTEGER);

CREATE TRIGGERS in MySQL

Let’s see some examples of using MySQL CREATE trigger

MySQL INSERT TRIGGER

We can create triggers for row INSERTS in an existing table. There can be 2 types of such triggers namely AFTER INSERT and BEFORE INSERT.

AFTER INSERT TRIGGER

We will create an AFTER INSERT trigger on the table mentioned in test data – item_inventory.

Let’s create an inventory audit table first.

CREATE TABLE item_inventory_audit (id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
item_id VARCHAR(30), price DECIMAL(5,2), created_on DATETIME(6), quantity INTEGER);

We will create a TRIGGER to add an entry in the item_inventory_audit table after every INSERT in the item_inventory table.

CREATE TRIGGER trigger_tutorial.trigger_item_insert_audit
AFTER INSERT
ON trigger_tutorial.item_inventory
FOR EACH ROW
INSERT INTO trigger_tutorial.item_inventory_audit(item_id,price,created_on,quantity) VALUES(new.item_id, new.price, now(), new.quantity);

In the above TRIGGER definition

• We’ve created a trigger named – trigger_item_insert_audit in database – trigger_tutorial.
• The trigger is created on the table – item_inventory FOR EACH INSERT.
• The trigger body – contains an INSERT statement which would mean that after INSERT of the row in item_inventory table, a new row would be inserted in item_inventory_audit table.

We can also check if the trigger was successfully created by navigating to the triggers folder in the respective table on any MySQL GUI client like MySQL Workbench.

Let’s try to run this.

We will INSERT a record in the item_inventory table.

INSERT INTO trigger_tutorial.item_inventory(name, price, quantity) VALUES("Henko Washing Powder",150.50,100);

We can now check if the corresponding record in the item_inventory_audit table has been created or not.

SELECT * FROM trigger_tutorial.item_inventory_audit;

BEFORE INSERT TRIGGER

The Before INSERT triggers would be called BEFORE every row INSERT on the target table.

The syntax for Trigger creation remains the same, as we saw in the previous section.

BEFORE INSERT can be used when you want to update the values being inserted to some other value or default etc.

DELIMITER $$
CREATE TRIGGER trigger_tutorial.trigger_item_before_insert
BEFORE INSERT
ON trigger_tutorial.item_inventory
FOR EACH ROW
BEGIN
IF new.price > 100 THEN SET new.price = 1.02 * new.price;
END IF;
END $$ 
DELIMITER ;

Suppose we want to have the price in the audit table to be 2% more than the actual price data being entered.

Here, we have created a BEFORE INSERT trigger, which would change the price of the new row being added to 1.02 times the original price.

INSERT INTO trigger_tutorial.item_inventory(name, price, quantity) VALUES("Tomato Ketchup",120,20);

SELECT * FROM trigger_tutorial.item_inventory_audit where item_id=2;

MySQL UPDATE TRIGGER

We can create triggers for the row UPDATE of an existing table. Similar to INSERT, for UPDATE as well, we can have 2 types of triggers i.e. BEFORE UPDATE and AFTER UPDATE. Let’s see examples for both such cases.

In the UPDATE trigger – we can retrieve both NEW and OLD values for the item being updated using new and old keywords respectively.

Let’s create a BEFORE UPDATE Trigger which would set the quantity to be updated in the NEW row to be the difference between the OLD and NEW value. Example, if someone is updating the quantity column in the existing table, then

• If new.quantity < old.quantity => set new.quantity = old.quantity – new.quantity
• Else new.quantity = 0

DELIMITER $$
CREATE TRIGGER trigger_tutorial.trigger_item_before_update
BEFORE UPDATE
ON trigger_tutorial.item_inventory
FOR EACH ROW
BEGIN
	IF old.quantity > new.quantity THEN SET new.quantity = old.quantity - new.quantity;
    ELSE SET new.quantity = 0;
    END IF;
END $$ 
DELIMITER ;

Let’s also create an AFTER UPDATE Trigger which would INSERT a new row in the audit table with the new quantity column value calculated in the BEFORE UPDATE TRIGGER as shown above.

DELIMITER $$
CREATE TRIGGER trigger_tutorial.trigger_item_after_update
AFTER UPDATE
ON trigger_tutorial.item_inventory
FOR EACH ROW
BEGIN
    INSERT INTO trigger_tutorial.item_inventory_audit(item_id,price,created_on,quantity) VALUES(old.id, old.price, now(), new.quantity); 
END $$ 
DELIMITER ;

Let’s now understand this with the help of an example.

Suppose we did an initial INSERT in the item_inventory table using the below query:

INSERT INTO trigger_tutorial.item_inventory(name, price, quantity) VALUES("Cookies",90,60);

This would have triggered the BEFORE INSERT and AFTER INSERT Triggers respectively.

Now let’s run an UPDATE for this Item (which bears an item ID – 2)

UPDATE trigger_tutorial.item_inventory SET quantity=20 WHERE id=2;

Now the sequence of trigger execution for UPDATE would be

=> UPDATE STATEMENT => BEFORE UPDATE TRIGGER => AFTER UPDATE TRIGGER

The Logic in BEFORE UPDATE TRIGGER – would update the quantity of new row to the difference of the new and original values

Since we are updating the quantity to 20 and the original value was 60, the actual value that would get inserted into the table would be 40.

Let’s do a SELECT on the inventory_audit table to verify the results.

SELECT * FROM trigger_tutorial.item_inventory_audit WHERE item_id=2

The first row you see above is added by the INSERT Trigger.

The second row is added as part of UPDATE

1. UPDATED quantity = 20
2. BEFORE UPDATE TRIGGER => changes new.quantity = 60 – 20 => 40
3. AFTER UPDATE TRIGGER => Inserts a row in audit_table with updated quantity – i.e 40

MySQL DELETE TRIGGER

In order to delete or drop MySQL TRIGGER, we can use the DROP TRIGGER command.

Syntax

DROP TRIGGER [if exists] schema.trigger_name;

Let’s see an example command:

Suppose we want to remove the BEFORE INSERT trigger we created in previous sections. The trigger was named as trigger_tutorial.trigger_item_before_insert

Let’s use the DROP command to delete this:

DROP TRIGGER trigger_tutorial.trigger_item_before_insert;

Once the trigger is deleted, any actions associated with the trigger would be void and no such events would be raised.

Listing All Triggers Associated With Table

In order to list all the triggers associated with a schema or database, you can use the SHOW TRIGGER command.

For Example: If we want to list all the triggers in the trigger_tutorial schema, then we can use the below command.

SHOW TRIGGERS FROM trigger_tutorial;

//Output

You can see lots of different details related to the different triggers that are available in the schema.

A couple of such column details are shown below:

Creating Triggers Using MySQL GUI Client – MySQL Workbench

Similar to creating using the CREATE TRIGGER event, triggers can also be created using the MySQL GUI Clients like MySQL Workbench.

Let’s see the steps to create a BEFORE INSERT Trigger using MySQL Workbench.

#1) Right-Click on the table name where the trigger needs to be created.

#2) Select the option “Alter Table”

#3) Create “Triggers Tab”

#4) Click on the ‘+’ sign adjacent to the BEFORE INSERT section to create a trigger.

#5) On the right side, update/edit the definition of the trigger. We will add an IF statement to set the price = 1.02 * price for price > 100

IF new.price > 100 THEN SET new.price = 1.02 * new.price;
END IF;

#6) Click “Apply” to Save the Trigger.

#7) The trigger is now created. To verify, you can run the SHOW TRIGGERS command and validate if the newly created trigger is displayed.

SHOW TRIGGERS FROM trigger_tutorial;

Similar to BEFORE INSERT, we can add/delete other types of triggers like AFTER INSERT, BEFORE/AFTER UPDATE, BEFORE/AFTER DELETE through MySQL Workbench GUI Client as well.

Error Handling During Trigger Execution

It’s important to understand how MySQL handles errors that occurred during trigger execution.

• For triggers that execute BEFORE or AFTER an operation – If the trigger execution fails, the operation on the row would not be performed.
• For triggers that are defined for both BEFORE and AFTER operation – If the BEFORE trigger fails, then the operation would be failed and the AFTER trigger would not be called.

Advantages of Using Triggers

• Faster application development and code reuse: As triggers are named objects within the database, once created they could be reused and can also be altered easily as per the business needs.
• Improved performance: All defined rules within the trigger execute on the server before the result is returned. This prevents repeated back and forth between client and server.

Frequently Asked Questions

Q #1) What are Triggers in MySQL?

Answer: Simply stated, A trigger in MySQL is a kind of action in response to any defined event.

For example, Suppose we want to perform some action (in the form of executing some statements/insert data in another table) on INSERTING or UPDATING a row in some other table, we can use TRIGGERS for that purpose.

Q #2) How are Triggers implemented in MySQL?

Answers: Triggers are named objects within MySQL DATABASE i.e. Trigger is associated with a table in MySQL database.

The triggers can be created using CREATE TRIGGER command and can be one of 2 types:

• Row-level: These are called for each row. For example, on every INSERT of the row in the table.
• Statement level: These are called once during the statement execution. The statement might impact one or more rows but the associated trigger would be called just once.

Q #3) Can there be Multiple Triggers associated with the same event?

Answer: Yes, it’s perfectly fine to have multiple triggers associated with the same event. For example, we can define multiple triggers to get executed when a row is INSERTED into a table.

The order of the trigger execution is by default in the order in which Triggers were created. This sequence can also be changed, using the trigger_order field while creating the trigger.

Q #4) Is it possible to update an existing trigger?

Answer: In MySQL, there’s no statement to update or alter an existing trigger. If after trigger creation, you need to change the trigger. You can DROP the existing trigger using the DROP TRIGGER command and create a new one with the same name.

Conclusion

In this tutorial, we learned about the concept of using TRIGGERS in MySQL. Triggers allow a user to define a custom action in response to a predefined event like INSERT or UPDATE of a row in another table etc.

Triggers are very helpful in various use cases, such as:

• Adhering to supported values/data validation.
• Error handling
• Enabling audit logs for all change events happening on a particular table.

Triggers also largely support code reuse and optimize performance by reducing back-and-forth network calls for individual updates.

=> Visit Here To Read The MySQL Tutorial Series