How to Work with Triggers on Alibaba Cloud MySQL Database

In this tutorial, we will take you through the steps of creating and implementing triggers on your MySQL server hosted on an Alibaba Cloud ECS instance or ApsaraDB RDS for MySQL.

By Francis Ndungu, Alibaba Cloud Community Blog author.

Triggers are predefined user functions that are stored on the database server. The stored programs are activated once a certain database event occurs, such as during an insert, update or delete operation.

Since they reside on the database, triggers eliminate the need of maintaining codes for running repetitive tasks on the client side. A great example is when a trigger is used in production environment to compute the moving average of bank balance for clients.

On top of security, high-availability fast RAMs and latest CPUs, Alibaba Cloud MySQL servers hosted on the ECS instances or ApsaraDB for RDS support triggers.

Thus, running your database on China's biggest cloud computing company guarantees you all the benefits of MySQL triggers. These include security checks, data auditing, improved integrity, and ability to store some of the application logic on the database server.

This is a step-by-step tutorial of creating and implementing triggers on your MySQL server hosted on an Alibaba Cloud ApsaraDB RDS for MySQL or Elastic Compute Service (ECS) instance.

Prerequisites

Before you begin, make sure you have the following:

An Alibaba Cloud Account. You may create a new account if you don't have and get up to $1200 worth of free credit to test over 40 Alibaba Cloud products.
MySQL server either running on Alibaba Cloud ApsaraDB or ECS instance
The root password for login to your MySQL database server

Procedure

Step 1: MySQL Trigger Syntax

Before, we create our first trigger, we need to understand the correct syntax as shown below:

DELIMITER $$
CREATE TRIGGER [Trigger Name] [Trigger Time] [Trigger Event]
 ON [Table Name]
 FOR EACH ROW
 BEGIN
 [SQL CODE]
 END;
DELIMITER ;

Let us go ahead and analyze the building blocks of MySQL triggers in details:

DELIMITER: By default, MySQL considers a semicolon (;) as the end of an SQL statement. So, since we want to pass our entire block of the trigger as one statement, we have to temporarily change the delimiter to something else (such as $$) and later restore it back to (;).

This is the reason why our trigger block is enclosed inside the delimiter code:

DELIMITER $$
 [TRIGGER BLOCK]
DELIMITER ;

Trigger Name: You should come up with a descriptive name of your trigger. Most developers may find it cool to use the 'TR_TableName_ActionName' naming convention. Below are some examples of trigger names:
- TR_Products_Delete
- TR_SavingsAccounts_Update
- TR_OtherTable_Insert
Trigger Time: Triggers can fire before (BEFORE trigger) or after (AFTER trigger) a specified event has occurred. Another special type of timing is called INSTEAD OF trigger that controls operations on database views but it is beyond the scope of this guide.

So you can use any of the three trigger timings as listed below:

BEFORE
AFTER
INSTEAD OF
- Trigger Event: This is the database occurrence that causes the trigger to fire. This occurs during an INSERT, UPDATE or DELETE event. Also, a trigger can only get activated by a single event. So, if for instance, you want it to fire during an insert and update event, you must create two triggers.
- Table Name: This value specifies the table associated with a trigger. So, the database server will invoke the trigger once the timing and event specified occurs on the defined table.
- FOR EACH ROW: This line tells MySQL server to run the events on each row that gets affected by the trigger event.
- BEGIN¡END: This is where you put the SQL statement that you want to be executed by your defined trigger.

Step 2: Creating a Sample Database to Run Triggers

You now have a basic idea of how a MySQL trigger is coded. In order to prove the concept, we will create a sample database and add a table to store our sample data. So, log in to your MySQL server:

$ mysql -uroot -p

Enter your database root password when prompted and hit Enter to continue. Next create a database and named it 'xyz_bank':

mysql> create database xyz_bank;

Then, switch to the newly created database:

mysql> use xyz_bank;

Create a table named 'customers_savings' with the below schema:

mysql> 
create table customers_savings (
customer_id BIGINT,
transaction_date DATETIME,
debit DOUBLE,  
credit DOUBLE, 
balance DOUBLE
);

Verify the table schema by running the code below:

mysql> Describe customers_savings;

The output is as follows:

+------------------+------------+------+-----+---------+-------+
| Field            | Type       | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| customer_id      | bigint(20) | NO   |     | NULL    |       |
| transaction_date | datetime   | YES  |     | NULL    |       |
| debit            | double     | YES  |     | NULL    |       |
| credit           | double     | YES  |     | NULL    |       |
| balance          | double     | YES  |     | NULL    |       |
+------------------+------------+------+-----+---------+-------+

Step 3: Creating MySQL Trigger

Once our database schema is in place, we can now go ahead and create our first trigger to compute the moving balance of the customer's account.

DELIMITER $$
CREATE TRIGGER TR_CustomersSavings_Update BEFORE INSERT  
ON customers_savings
 FOR EACH ROW
 BEGIN
 SET NEW.balance =(select ifnull(sum(credit-debit),0) from customers_savings where customer_id=NEW.customer_id)+ (NEW.credit-NEW.debit);

 END $$
DELIMITER ;

The code above simply computes the moving balance of the customer's savings. Here, we are using a very simple formula. In order to get a new balance, we are checking the sum of all previous credits subtracted to debits. We then add the credit amount and subtract the debit amount of the new record to get the moving balance of the record being inserted.

In simple terms, the moving balance should be the sum of all previous records' balance plus credit (what the customer is depositing) minus debit(what the customer is withdrawing from the bank).

In order to let the database server know the customer records we are updating, we have used the NEW statement to retrieve the 'customer_id' since our table could be holding information for multiple customers.

To test if the trigger is working, we are going to insert some new rows for the customer_id '1271':

mysql>insert into customers_savings(customer_id, transaction_date, debit, credit)VALUES('1271',now(), '0', '10000');
mysql>insert into customers_savings(customer_id, transaction_date, debit, credit)VALUES('1271',now(), '2000', '0');
mysql>insert into customers_savings(customer_id, transaction_date, debit, credit)VALUES('1271',now(), '0', '6000');

We can verify the existence of the data by running the below SQL command:

mysql> select * from customers_savings;

The output is as follows:

+-------------+---------------------+-------+--------+---------+
| customer_id | transaction_date    | debit | credit | balance |
+-------------+---------------------+-------+--------+---------+
|        1271 | 2019-01-09 13:11:32 |     0 |  10000 |   10000 |
|        1271 | 2019-01-09 13:12:03 |  2000 |      0 |    8000 |
|        1271 | 2019-01-09 13:12:18 |     0 |   6000 |   14000 |
+-------------+---------------------+-------+--------+---------+

As you can see above, our data was inserted in the server and indeed, our trigger was able to compute the moving balance for each newly inserted record.

We can go ahead and verify if the trigger is able to differentiate different records from another customer. So, again, let us add two more records with a different customer_Id (such as 2380).

mysql>insert into customers_savings(customer_id, transaction_date, debit, credit)VALUES('2380',now(), '0', '40');
mysql>insert into customers_savings(customer_id, transaction_date, debit, credit)VALUES('2380',now(), '13', '0');

We have inserted two records. In the first record, the customer has deposited $40 (credit) and then withdrawn $13 (debit) as seen in the second insert command.

Let's check the data and see if our trigger was able to handle the computation accurately without mixing up the records. In the end, the new customer's record should have a balance of $27. That is $40 minus $13.

We can check this by running the command below:

mysql> select * from customers_savings where customer_id='2380';

The output is as follows:

+-------------+---------------------+-------+--------+---------+
| customer_id | transaction_date    | debit | credit | balance |
+-------------+---------------------+-------+--------+---------+
|        2380 | 2019-01-09 13:16:24 |     0 |     40 |      40 |
|        2380 | 2019-01-09 13:16:40 |    13 |      0 |      27 |
+-------------+---------------------+-------+--------+---------+

That's it, our MySQL trigger is working as expected.

Step 4: Dropping Triggers

If you want to completely delete the trigger from your database, you can do this using the MySQL DROP statement as shown below:

DROP TRIGGER [Trigger Name];

For example, to delete our 'TR_CustomersSavings_Update trigger', we should run the command below:

DROP TRIGGER TR_CustomersSavings_Update;

Benefits of Triggers

We have taken you through the steps of creating and deleting triggers on MySQL servers. We are now going to list some of their benefits:

Triggers can be used to enforce referential integrity in a database and make changes consistent in the entire database. Like in our sample above, we are able to log the moving balance of each customer during an insert statement.
Some of the business logic can be moved to the backend server when triggers are used, which means that the frontend coding will be less bulky.
Triggers may be used to keep logs for auditing purposes. When changes occur in the database, triggers may be used to save audit logs in a different table for future analysis.

Conclusion

In this tutorial, we have taught you how to create and implement database triggers on MySQL server hosted on Alibaba Cloud ECS instance or ApsaraDB for RDS. You can extend the samples given in this guide to suit your needs and create better database applications by automating repetitive tasks with triggers.

If you would like to test the sample code in this guide on a live server, we advise you to sign up for Alibaba Cloud and use the free trial credit of up to $1200. Then, spin a new database instance and see how it goes.

Community

How to Work with Triggers on Alibaba Cloud MySQL Database

Prerequisites

Procedure

Step 1: MySQL Trigger Syntax

Step 2: Creating a Sample Database to Run Triggers

Step 3: Creating MySQL Trigger

Step 4: Dropping Triggers

Benefits of Triggers

Conclusion

Read previous post:

Read next post:

francisndungu

You may also like

Comments

francisndungu

Related Products

Database Overview

AnalyticDB for MySQL

PolarDB for MySQL

ApsaraDB RDS for MySQL