Learning MariaDB Triggers

28/12/2020

How to use triggers in MariaDB

A trigger is a procedure that is fired automatically when any modification is done to a database table. There is no option to call or execute any trigger manually. The main purpose of using triggers is to maintain database integrity and it can be used with the referential integrity constraints which forces integrity rules. In a DBMS, when any insert, update, and delete event occurs then trigger procedure associated with the effected table will be executed automatically and does the required actions. Different types of triggers are supported by the database. Triggers are mainly categorized two ways. One is before trigger and another is after trigger. Before triggers are called before any event is executed in the table and after triggers are called after executing any event in the table. How you install MariaDB and apply different types of triggers in MariaDB database server on Ubuntu is shown in this tutorial.

MariaDB Installation:

Run the command below to update the system and install MariaDB server and client.

# sudo apt-get update && sudo apt-get install mariadb-server mariadb-client

Type ‘y’ and press enter to complete the installation process.

Run the following command to start MariaDB server.

# sudo systemctl start mariadb

Check the server is working properly or not. The output of the following command will show the details status of the server. The output indicates that MariaDB 10.1.30 verion is running.

# sudo systemctl status mariadb

Run the following command if you want to stop the server. Don’t run this command now.

# sudo systemctl stop mariadb

Setup the database and tables

You have to create a database and two or more tables to check how triggers work. First of all, run the mysql client to setup the database. It will ask for root password to access the database server.

# sudo mysql -u root

Create a database named store.

> create database store;

Select this new database:

> use store;

Create three tables in store database to apply trigger actions on these. Here, products, stock and stock_add tables are created.

CREATE TABLE products
( id INT(11),
name VARCHAR(30) NOT NULL,
price INT(11),
PRIMARY KEY (id)
);

CREATE TABLE stocks
( product_id INT(11),
opening_stock INT(11),
current_stock INT(11),
PRIMARY KEY (product_id)
);

CREATE TABLE stocks_add
( product_id INT(11),
entry_date DATE,
quantity INT(11),
PRIMARY KEY (product_id,entry_date)
);

Now, insert some data in these three tables.

Products:

Insert into products set id=101, name=’Bag’ , price=1000;
Insert into products set id=102, name=’Pen’ , price=100;
Insert into products set id=103, name=’Pencil’ , price=50;

stocks:

Insert into stocks set product_id=101, opening_stock=200 , current_stock=100;
Insert into stocks set product_id=102, opening_stock=230 , current_stock=150;
Insert into stocks set product_id=103, opening_stock=220 , current_stock=300;

stocks_add:

Insert into stocks_add set product_id=103, entry_date=’2018-01-01′,quantity=30;
Insert into stocks_add set product_id=103, entry_date=’2018-01-02′,quantity=50;
Insert into stocks_add set product_id=103, entry_date=’2018-01-03′,quantity=45;

Create After Trigger

You can create after trigger to do any action automatically after inserting or updating or deleting  records of a particular table. Here, products and stocks tables are selected to create after delete trigger. The data of stock table depends on the data of products table. So if any record is removed from the products table then the related records of stocks table need to be removed. Create the following trigger procedure to delete any related record from stocks table automatically when any record is removed from the products table. In this trigger, the deleted id is recognized by old.id.

DELIMITER //

CREATE TRIGGER products_after_delete
AFTER DELETE
ON products FOR EACH ROW

BEGIN
DELETE FROM stocks WHERE product_id = old.id;
END;
//


After trigger is created for products table. Now you have to test that trigger is working properly or not. Run the following query to remove a record from products where id is 101 and check the data of both products and stocks tables. After executing the query you will find that related record of stock table is removed by after trigger. No record for id value, 101, will be found in both tables.

> delete from products where id = 101;
> select * from products;
> select * from stocks;

Create Before Triggers

Before trigger is used to take any action before inserting or updating or deleting any or more records from a particular table. Here, stocks and stocks_add table are used to create before trigger. The value of current_stock of stocks table depends on quantity value of stocks_add table. If you update any quantity value of the stocks_add table then current_stock of stocks table need to be updated. So if any existing quantity value of stocks_add table decreases then current_stock of stocks will be decreased and if quantity value increases then current_stock will be increased. Create before update trigger for stocks_add table. In this trigger, the changed quantity value is calculated by subtracting old quantity from new quantity.

DELIMITER //
CREATE TRIGGER stocks_before_update
BEFORE UPDATE
ON stocks_add FOR EACH ROW

BEGIN

UPDATE stocks SET current_stock=current_stock+(new.quantity-old.quantity)
WHERE product_id = old.product_id;

END;
//

Before updating, check the current values of both stocks and stocks_add tables.

> select * from stocks;
> select * from stocks_add;


Suppose, you need to update quantity value of stocks_add table where product_id is 103 and entry_date is 2018-01-01 which is 30 now. If you want to update the value by 75 then run the following update query and check both tables again. The increased quantity is, 75-30=45. So, after update, the trigger will be fired and current_stock of stocks table will be set as, 300+45=345.

update stocks_add set quantity=75 where product_id=103 and entry_date=’2018-01-01′;
> select * from stocks;
> select * from stocks_add;

The use of two types of triggers are shown in this tutorial. In the similar way, you can create others types of before and after trigger for your database tables based on your requirements.

ONET IDC thành lập vào năm 2012, là công ty chuyên nghiệp tại Việt Nam trong lĩnh vực cung cấp dịch vụ Hosting, VPS, máy chủ vật lý, dịch vụ Firewall Anti DDoS, SSL… Với 10 năm xây dựng và phát triển, ứng dụng nhiều công nghệ hiện đại, ONET IDC đã giúp hàng ngàn khách hàng tin tưởng lựa chọn, mang lại sự ổn định tuyệt đối cho website của khách hàng để thúc đẩy việc kinh doanh đạt được hiệu quả và thành công.
Bài viết liên quan

Expose MySQL and MariaDB Database Server to the Internet

MariaDB/MySQL database server only runs on the IP 127.0.0.1 or the hostname localhost by default. So, it is not accessible...
29/12/2020

MariaDB Replication on RHEL 8/ CentOS 8

Disasters happen, and when they do, precious data can go with the wind, never again to be recovered, and when recovered,...
29/12/2020

Learning MariaDB Triggers

How to use triggers in MariaDB A trigger is a procedure that is fired automatically when any modification is done to a...
28/12/2020