MariaDB Create Triggers

Triggers are a very useful and powerful feature of MariaDB. It is a database object associated with a table that activates if an INSERT, UPDATE or DELETE operations are performed. The name itself reflects their action as they run immediately without any human intervention when the respective operations are performed.

In this article, you will learn about how to create triggers and use them easily so that we can automate complex tasks and perform data management easily.

MariaDB Create Trigger

MariaDB’s CREATE TRIGGER statement is a powerful tool in database management that allows us to define custom actions that are automatically performed when certain events occur in a table. This feature enables us to implement complex business logic and ensure data integrity rules within our database.

It is also used to replace a trigger with a trigger name which will be associated with a table whose name must be provided after the ON keyword. The INSERT, UPDATE or DELETE keywords are used to specify the event on which the trigger must get executed.

How to Write Triggers in MariaDB?

Triggers in MariaDB are database objects that automatically perform an action when a specified event occurs. In MariaDB, the Triggers are created with the help of CREATE OR REPLACE TRIGGER Statement.

Syntax:

CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name 
{BEFORE | AFTER } {INSERT | UPDATE | DELETE}
ON table_name 
FOR EACH ROW
[WHEN]
triggger_body;

Explanation: This query creates a trigger named trigger_name that activates either before or after an insert, update, or delete operation on table_name. The triggger_body contains the actions to be executed for each row affected by the operation. The OR REPLACE clause allows the trigger to be redefined if it already exists, and the IF NOT EXISTS clause prevents an error if the trigger already exists.

Example of CREATE TRIGGER Statement

To understand How to Delete a Trigger in MariaDB we need tables on which we will perform various operations and queries. Here we will consider a table called products and inventory_log where the products table contains id, name, price, stock and created_at as Columns. Also The inventory_log table contains id, product_id, action, quantity and timestamp as Columns.

The Below example shows the creation of triggers for all the three operations INSERT, UPDATE and DELETE.

The following query creates a table products with multiple columns having respective data types and constraints.

Structure of products table looks below:

Creation of Products table

Structure of inventory_log table looks below:

Creation of inventory_log table

Explanation: The query creates a table named as inventory_log with id, product_id, action, quantity and timestamp with respective constraints.

Let’s Inserts some sample records to the products table.

INSERT INTO products (name, price, stock) 
VALUES ('T-shirt', 19.99, 50),
('Mug', 9.99, 20),
('Hat', 14.99, 30);

Output:

Insertion of Values into products table

Explanation: The query inserts 3 rows to the products table with values in the name, price and stock column. The id column auto increments and the created_at auto enters the defaults value which is the current timestamp of insertion.

Example 1: Creating trigger for INSERT operation

The query creates a trigger named as track_inventory_insert for the INSERT operation in the products table.

CREATE TRIGGER track_inventory_insert AFTER INSERT ON products
FOR EACH ROW
INSERT INTO inventory_log (product_id, action, quantity, timestamp)
VALUES (NEW.id, 'added', NEW.stock, NOW());

Output:

Creating trigger for INSERT operation

Explanation: The query creates a trigger named as track_inventory_insert after the INSERT event in the products table. The statement carried out by the triggers is a INSERT operation to the inventory_log table with values in the product_id, action, quantity and timestamp columns.

Let’s Testing a Trigger with an INSERT Operation

The following query inserts a new row to the products table.

INSERT INTO products (name, price, stock) VALUES ('Pen', 2.99, 100);

Output:

Insertion of Values into products table

Explanation: As we see in the above image the new record in the product table. Also we have seen in the below image that reflect that the one record has been reflect in inventory_log Trigger.

INSERT operation in products table triggered INSERT operation in inventory_log table

Explanation: The INSERT operation in products table executed the track_inventory_insert trigger which ran the respective INSERT operation in inventory_log table.

Example 2: Creating trigger for UPDATE operation

The query creates a trigger named as track_inventory_update for the UPDATE operation in the products table.

CREATE TRIGGER track_inventory_update BEFORE UPDATE ON products
FOR EACH ROW
INSERT INTO inventory_log (product_id, action, quantity, timestamp)
VALUES (NEW.id, 'updated', NEW.stock - OLD.stock, NOW());

Output:

Creating trigger for UPDATE operation

Explanation: The query creates a trigger named as track_inventory_update before the UPDATE event in the products table. The statement carried out by the triggers is a INSERT operation to the inventory_log table with values in the product_id, action, quantity and timestamp columns.

Let’s Testing a Trigger with an UPDATE Operation

The following query updates a row in the products table.

UPDATE products SET stock = 75 WHERE name = 'T-shirt';

Output:

Updation of Values into products table

Explanation: As we see in the above image the UPDATE Operation is performed to update record in the product table. Also we have seen it reflect in the below image in inventory_log table.

UPDATE operation in products table triggered INSERT operation in inventory_log table

Explanation: The UPDATE operation in products table executed the track_inventory_update trigger which ran the respective INSERT operation in inventory_log table.

Example 3: Creating trigger for DELETE operation

The query creates a trigger named as track_inventory_delete for the DELETE operation in the products table.

CREATE TRIGGER track_inventory_delete BEFORE DELETE ON products
FOR EACH ROW
INSERT INTO inventory_log (product_id, action, quantity, timestamp)
VALUES (1, 'removed', OLD.stock, NOW())

Output

Creating trigger for DELETE operation

Explanation: The query creates a trigger named as track_inventory_delete before the DELETE event in the products table. The statement carried out by the triggers is a INSERT operation to the inventory_log table with values in the product_id, action, quantity and timestamp columns. The product_id value is hard coded to 1 due to foreign constraints which can be removed or modified to allow NULL values

Testing a Trigger with an DELETE Operation

The following query deletes a row in the products table.

DELETE FROM products WHERE name = 'Hat';

Output:

Deletion of Values into products table

Explanation: After perform DELETE Operation on the products table, we have seen that the reflect also saw in the inventory_log table.

DELETE operation in products table triggered INSERT operation in inventory_log table

Explanation: The DELETE operation in products table executed the track_inventory_delete trigger which ran the respective INSERT operation in inventory_log table. The product_id value is hard coded to 1 due to foreign constraints which can be removed or modified to allow NULL values

Conclusion

Triggers are a powerful mechanism to automate data management tasks that ensures data integrity and perform complex operations. By reading through the article you understand how to create triggers and how it can be used to automate data management tasks. It is important for you to clearly understand the triggers and its usages because irresponsible use can lead to performance issues or some lead to unwanted data loss or modification.