MySQL Trigger

Introduction to MySQL Trigger

A Trigger in MySQL is a special kind of stored operation that gets invoked automatically when an event has occurred in the database. A trigger is a database object which is related to a table in the database and becomes active when a defined MySQL statement is initiated on the table. These DML (Data Manipulation Language) execution operations can be INSERT, DELETE, UPDATE and triggers can be called before or after these events.

For example when a row is inserted to a table or when any columns are modified, a trigger can be fired. Mostly, triggers can are made to run whenever any alterations are done to the data of a table. Triggers are simply a SQL code to run before or just after any DML action events on a particular table in a database.

MySQL has supported Triggers since version 5.0.2. So, triggers are stored and managed by DBMS as they event-driven SQL procedures.

Syntax

To create a new trigger in MySQL we use the statement CREATE TRIGGER:

CREATE
[DEFINER = user] TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

Parameters

• DEFINER clause: Identifies the MySQL account that is used for access at trigger initiation time.
• trigger_name: The name of all triggers should be unique inside a schema.
• trigger_time: Defines the trigger action time which can be either before or after any row affected.
• trigger_event: Specifies the type of operation to activate the trigger.
• tbl_name: The table name should be of a permanent table not a temporary or a view table to associate a trigger.
• trigger_body: It is used to display a statement when the trigger is fired. The compound statement construct BEGIN … END can be used to complete many statements.

This syntax is used to drop a trigger.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

The schema name is optional which if omitted; drops the trigger from the default schema. The DROP TRIGGER statement needs the privilege of the trigger for the table that is associated with the trigger. IF EXISTS is used to check if a trigger exists or not so that it prevents any error to have occurred.

Types of Trigger in MYSQL

Two types of triggers are defined by the SQL standard:

• Row-Level Trigger: It is executed when each row is affected by insertion, updation and deletion actions on a table. Like if you have a table and 50 rows are inserted, updated or deleted, then automatically the trigger is also invoked for 50 times.
• Statement-Level Trigger: This trigger is invoked only once for a transaction regardless of the number of rows inserted, updated, or deleted.

Row-Level Trigger is supported in MySQL but not Statement-Level Trigger. So, the following are various types of triggers in MySQL:

1. Data Manipulation Language (DML) Triggers

These triggers are called when any DML queries like INSERT; UPDATE OR DELETE is executed on a Table or View.

Handling MySQL Triggers:

BEFORE INSERT trigger: Specifies to keep a summary table from a table before insertion is done.

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name FOR EACH ROW
trigger_body;

For example, lets’ add a trigger on the Products table below:

Here, the trigger applied will insert Price = 10 automatically when we try to insert Price < 10.

DELIMITER //
Create Trigger before_inser_product_price BEFORE INSERT ON Products FOR EACH ROW
BEGIN
IF NEW.Price < 10 THEN SET NEW.Price = 10;
END IF;
END //

Now, for testing the trigger we can execute the following statements:

INSERT INTO Products(ProductName, SupplierID, CategoryID, Unit, Price) values('Haldiram Namkeen',3,1,'10 boxes x 12 pieces',9.20);

And then we display the product list:

Select * from Products;

Output:

We can see that when we insert a product with price value less than 10 in the table the trigger will automatically insert 10 to its price, i.e.

5      Haldiram Namkeen    3       1       10 boxes x 12 pieces   10

This is an example of a trigger with a combination of the trigger_event INSERT and the trigger _time BEFORE.

AFTER INSERT trigger: Triggers after inserting data into a table.

CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name FOR EACH ROW
trigger_body

For Eg, on the same Products table, we want to add a few info in other table log_products that have three fields ( ProductID, ProductName, Price ) each time when an insertion is made on Products table by the After Trigger.

DELIMITER //
Create Trigger after_inser_product_info AFTER INSERT ON Products FOR EACH ROW
BEGIN
INSERT INTO log_products  VALUES(NEW.ProductID, NEW.ProductName,NEW.Price, NOW());
END //

Output:

BEFORE UPDATE trigger: When an update statement is written, it validates data before the update is executed.

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body

For example, we set a trigger on the Orders table for before update operation:

DELIMITER //
Create Trigger before_updat_order_quantity BEFORE UPDATE ON Orders FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
NEW.Quantity,   ' cannot be 2 times greater than the current quantity : OLD.Quantity);
IF new.Quantity > old.Quantity * 2 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
END //

Now, if we pass query:

UPDATE Orders SET Quantity = 70  WHERE OrderID = 2;

Here, the trigger causes a violation of ruleset and MYSQL raises an error.

Output:

AFTER UPDATE trigger: Trigger is invoked after the update statement is implemented.

CREATE TRIGGER trigger_name
AFTER UPDATE
ON table_name FOR EACH ROW
trigger_body

E.g. For the same table Orders, we insert After Update trigger and note the changes on other table Orderchanges(OrderID, ProductID, BeforeQuantity, AfterQuantity).

DELIMITER //
CREATE TRIGGER after_order_update
AFTER UPDATE
ON Orders FOR EACH ROW
BEGIN
IF OLD.Quantity <> new.Quantity THEN
INSERT INTO Orderchanges(OrderDetailID, ProductID , BeforeQuantity,AfterQuantity)
VALUES(old.id, old.Quantity, new.Quantity);
END IF;
END//

It checks if the new value is not equal old value, then makes changes to the Orderchanges table. Suppose,

UPDATE Orders SET Quantity = 70 WHERE OrderID = 2;

Output:

BEFORE DELETE trigger: Specifies trigger before any delete statement is executed.

CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
trigger_body

E.g. let’s take the same Products table:

DELIMITER //
CREATE TRIGGER before_cust_del
BEFORE DELETE
ON Products FOR EACH ROW
BEGIN
INSERT INTO ProductsArchive(ProductName, Price)
VALUES(old.ProductName, old.Price);
END//

Finally, we will insert the deleted row before deleting it into the ProductsArchive table. So,

DELETE From Products WHERE ProductID = 3;
SELECT * From ProductsArchive ;

Output:

AFTER DELETE trigger: Specifies trigger after any delete statement is executed.

CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
trigger_body;

Let us consider the previous Products table. Now we have created other table Product Sum where we have operated query ( SUM(Price) )for the total price of products on Products table and inserted into it:

SELECT * FROM ProductSum;

Now, trigger created

DELIMITER //
CREATE TRIGGER after_prod_del
AFTER DELETE
ON Products FOR EACH ROW
BEGIN
UPDATE ProductSum SET Sum = Sum – old.Price;
END//

If we delete a row from Products table then, total sum on ProductSum will be affected by the trigger:

DELETE FROM Products WHERE ProductID =2 ;
SELECT * FROM ProductSum;

Output:

Create multiple triggers for a table that have the same trigger event and time: A trigger that is invoked before or after a current trigger having the same event and action time.

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE}
ON table_name FOR EACH ROW
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
-- statements
END$$
DELIMITER ;

Show triggers: Show all triggers in a particular database or table using FROM, IN keyword, or any pattern matching clause.

SHOW TRIGGERS
[{FROM | IN} databasename] [LIKE 'pattern' | WHERE searchcondition];

2. Data Definition Language (DDL) Triggers

These triggers are invoked when any DDL operations like CREATE, DROP, ALTER, DENY, GRANT, UPDATE STATISTICS and REVOKE statements are called.

3. LOGON Triggers

These triggers get fired automatically to respond to a LOGON event. Suppose before a user session is established, the trigger is invoked after successful authentication only but if authentication is failed then the trigger will not be executed.

4. CLR Triggers

These Triggers are useful if a heavy computation is required in the trigger or may refer to object outside SQL. The trigger is built on the SQL CLR. The supported .NET CLR languages like C#, VB.NET, etc. can be used to write DML and DDL triggers 

Conclusion

Yes, here we can say that a trigger is a small amount of relative energy which releases a large amount of energy. However, a trigger may increase the server workload but it helps in the field of Data integrity with referential constraints and check constraints. Also, it is capable to handle any sort of error on the database layers. The Trigger in MYSQL is also useful to run a scheduled task automatically.

Recommended Articles

This is a guide to MySQL Trigger. Here we discuss the introduction to Trigger in MYSQL along with the types of triggers and respective Syntax, Parameters & examples. You can also go through our other related articles to learn more–

1. PL/SQL Data Types
2. Talend Data Integration
3. Different Commands of PowerShell Scheduled Task
4. Complete Guide to Triggers in SQL
5. MySQL IN Operator | Examples
6. Guide to MySQL Timestamp