This article is first published: kalacloud.com/blog/how-to…

Trigger is a very useful feature in MySQL. It can be triggered before (or after) the operator “adds or deletes” a table and automatically executes a piece of written SQL code.

This tutorial takes you through hands-on learning, and you will learn about the important use of triggers in real-world scenarios.

In this tutorial, you are a programmer at Kara Cloud Bank and you are building a customer management system for your bank. In this system, you need to set up a system to detect whether the operator enters incorrect data before the INSERT table, log the operator’s behavior during the UPDATE, and determine whether the deleted information complies with the DELETE rule when the DELETE is performed. All three types of operations can be implemented using MySQL triggers.

If you are building a database management tool or an internal enterprise tool on top of a database, I recommend you to try my Cara Cloud, see below.

This tutorial will take you through practical cases

  • BEFORE INSERT: Checks whether the inserted data complies with the service logic. If the inserted data does not comply with the service logic, an error message is displayed.
  • AFTER INSERT: After an account is created in table A, the creation success information is automatically written to table B.
  • BEFORE UPDATE: Checks whether the updated data complies with the service logic. If the updated data does not comply with the service logic, an error message is displayed.
  • AFTER INSERT: Records operations in logs after data is updated
  • BEFORE DELETE: Check whether associated data exists before deleting data. If yes, stop the deletion.
  • AFTER DELETE: After information in table A is deleted, information associated with table A in table B is automatically deleted.

A prerequisite for

Before you start, make sure you have the following conditions:

  • A configured Ubuntu server with the root account.
  • MySQL > install MySQL on MySQL Server
  • MySQL root account

Creating a sample database

Let’s start by creating a clean sample database that you can follow along with this tutorial. We will demonstrate the various ways MySQL triggers work in this database.

First, log in to your MySQL server as root:

mysql -u root -p
Copy the code

When prompted, ENTER the password of your MySQL root account and press ENTER to continue. After seeing mysql> prompt, run the following command to create the demo_kalacloud database:

CREATE database demo_kalacloud;
Copy the code
Output
Query OK, 1 row affected (0.00 sec)
Copy the code

Next, switch to the new demo_Kalacloud database:

USE demo_kalacloud;
Copy the code
Output
Database changed
Copy the code

Next, create a Customers table. We use this table to keep track of bank customers. This table contains customer_id, customer_NAME, and Level. Let’s first divide customers into two levels: BASIC and VIP.

create table customers(
customer_id BIGINT PRIMARY KEY, 
customer_name VARCHAR(50), 
level VARCHAR(50) 
) ENGINE=INNODB;
Copy the code
Output Query OK, 4 rows affected (0.01sec)Copy the code

Next, we add some customer records to the Customers table.


Insert into customers (customer_id, customer_name, level )values('1'.'Jack Ma'.'BASIC');
Insert into customers (customer_id, customer_name, level )values('2'.'Robin Li'.'BASIC');
Insert into customers (customer_id, customer_name, level )values('3'.'Pony Ma'.'VIP');
Copy the code

After running three separate INSERT commands, the command line outputs success information.

Output Query OK, 1 row affected (0.01sec)Copy the code

We use SELECT to check if three pieces of information have been written to the table:

Select * from customers;
Copy the code

Let’s create another table, customer_STATUS, to hold the remarks for customers in the Customers table.

This table contains the customer_id and status_notes fields:

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;
Copy the code

Then, we create a SALES table, which is associated with customer_id. Keep sales data related to customers.

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;
Copy the code
Output Query OK, 4 rows affected (0.01sec)Copy the code

In the last step, we create another audit_log table to record the operation behavior of operators when they operate the customer management system of “Carla Cloud Bank”. This allows the administrator to check logs when problems occur.

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;
Copy the code
Output Query OK, 0 rows affected (0.02sec)Copy the code

At this point, you, as a programmer of “Kara Cloud Bank”, have completed the demo_Kalacloud database and four tables of the customer management system. Next, we will add corresponding triggers to the key nodes of the management system.

Mysqldumpslow: How to Use slow query logs for performance Profiling

1.BEFORE INSERTUse of trigger

As a strict bank customer management system, any data written into the system should be tested in advance to prevent the wrong information from being written in.

To detect data BEFORE writing, we can use the BEFORE INSERT trigger.

When an operator writes to the SALES_AMOUNT field in table SALES, the data is checked for compliance before INSERT.

Let’s take a look at the basic syntax for creating triggers.

DELIMITER //
CREATE TRIGGER[Name of trigger] [trigger execution timing] [Object monitored by trigger]ON[table name]FOR EACH ROW[Trigger body code]//
DELIMITER ;
Copy the code

The structure of the trigger includes:

  • DELIMITER //The MySQL default delimiter is;But in triggers, we use//Represents the start and end of a trigger.
  • [Name of trigger]: Fill in the name of the trigger
  • [Trigger execution time]: Sets whether the trigger fires before or after the key action is executed.
  • [The object monitored by the trigger]: Triggers can be monitoredINSERT,UPDATE,DELETEWhen the monitored command operates on the table associated with the trigger, the trigger is activated.
  • [table name]: Associates the trigger with a table in the database. The trigger is defined on and attached to the table. If the table is dropped, the trigger is also dropped.
  • FOR EACH ROWThe trigger will execute as long as the trigger condition is met. The trigger will monitor each row of code that operates on the associated table and fire if the condition is met.
  • [Trigger body code]: Here is the body of code that is triggered to execute when the trigger condition is met. This can be a single SQL statement or a multi-line command. If the command is multi-line, the command should be written inBEGIN... ENDIn between.

** Note: ** When creating trigger bodies, you can also use OLD and NEW to retrieve data written to SQL before and after INSERT, UPDATE, and DELETE operations. It doesn’t matter if you don’t understand it, but we’ll expand on that in the next practice.

At this point, you’ve seen a lot of concepts in the clouds, so don’t worry if you don’t understand them. It’s time to get into practice. Just follow the code and see what comes back.

Now, let’s create the first trigger, BEFORE INSERT (execute the trigger BEFORE INSERT). This trigger is used to monitor whether the sales_amount value in table SALES is greater than 10000 when the operator writes to it, and if so, returns an error message.

After logging in to MySQL Server, we create a trigger:

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT ="The total sales you entered exceeds 10,000 yuan." ;END IF//
DELIMITER ;
Copy the code

In this code, we use IF… THEN… END IF to create a trigger that monitors whether the values written by the INSERT statement are within the defined range.

The function of this trigger is to monitor whether the NEW value (> 10000) meets the condition when INSERT writes sales_amount.

When the operator enters a number greater than 10000, the following error message is returned:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You entered the total amount of sales exceeds 10000 yuan. ';Copy the code

Let’s try to see if the trigger is enabled.

We insert a value of 11000 into sales_amount.

Insert into sales(sales_id, customer_id, sales_amount) values('1'.'1'.'11000');
Copy the code

The command line returns an error message that we filled in when we created the trigger. Consistent with our setup.

Insert a number less than 10000:

Insert into  sales(sales_id, customer_id, sales_amount) values('1'.'1'.'7700');
Copy the code

If the value is 7700 and less than 10000, the insert command is executed successfully.

Output Query OK, 1 row affected (0.01sec)Copy the code

Let’s call up table SALES and see if it was successfully inserted:

Select * from sales;
Copy the code

Output confirmation data in the table:

! [confirm the data in the table]] (kalacloud.com/static/6a4d…).

From this table, we can see that 7700 has been inserted into the table.

After demonstrating that a value meets its setting before an INSERT command is executed, let’s look at using triggers to save different values to different tables after an INSERT.

How to migrate MySQL/MariaDB database between two servers

2.AFTER INSERTUse of trigger

AFTER INSERT, the trigger executes the code set up in the trigger AFTER it detects that we have successfully executed INSERT.

For example, in the bank account system, when we create a new account, we write the creation success message to the corresponding customer_STATUS table.

In this case, you, as a programmer for “Cara Cloud Bank”, are now creating an AFTER INSERT trigger that writes success information to the customer_STATUS table AFTER creating a new customer account

To create an AFTER INSERT trigger, type the following command:

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'Account created successfully')//
DELIMITER ;
Copy the code
Output
Query OK, 0 rows affected (0.00 sec)
Copy the code

This trigger writes success information to the row corresponding to the CUSTOMer_STATUS table after the operator inserts new customer information into the CUSTOMERS table.

Now let’s INSERT a message to see if the trigger is enabled:

Insert into customers (customer_id, customer_name, level )values('4'.'Xing Wang'.'VIP');
Copy the code
Output Query OK, 1 row affected (0.01sec)Copy the code

Log the INSERT success, and then check to see if the customer_STATUS table has written the corresponding success data.

Select * from customer_status;
Copy the code

As you can see here, we insert a new user with a customer_id of 4 into the Customers table, and then the trigger automatically inserts an account success message with a customer_id of 4 into the Customer_STATUS table according to the code.

AFTER INSERT is particularly well suited for associated writes of such state changes. Such as account opening, suspension, logout and other status changes.

Now that we’ve covered the use of triggers before and after INSERT, let’s move on to UPDATE triggers.

MySQL configuration file my.cnf/my.ini

3.BEFORE UPDATEUse of trigger

BEFORE UPDATE triggers are very similar to BEFORE INSERT triggers in that they do a check of business logic BEFORE updating data to avoid misoperations.

When we created the sample database earlier, we created two levels of customer, VIP and BASIC. Once cara Cloud customers upgrade to VIP, they can no longer downgrade to BASIC.

To enforce this rule, we use BEFORE UPDATE, a trigger that determines whether the UPDATE statement is degraded BEFORE it executes, and if so, prints an error message.

Let’s create this trigger:

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP customers cannot be downgraded to ordinary customers';
END IF //
DELIMITER ;
Copy the code

We can use OLD to get the level value of the customer before the UPDATE command is executed. Again, we use the IF… THEN… The END IF statement is used to determine whether the level value complies with the rule.

Let’s first look at the data in the Customers table.

select * from customers;
Copy the code

Well, let’s take a VIP customer and demote him to see if our trigger works correctly.

Next, run the following SQL command to see if you can demote VIP customer with customer_ID 3 to BASIC customer:

Update customers set level='BASIC' where customer_id='3';
Copy the code

After executing the code, the command line returns an error message:

This means that the trigger we just set is working.

Let’s try running the same command on a BASIC level customer and see if we can upgrade him to VIP:

Update customers set level='VIP' where customer_id='2';
Copy the code

Successful execution:

Output
Rows matched: 1  Changed: 1  Warnings: 0
Copy the code

Let’s look at the data in the Customers table again:

select * from customers;
Copy the code

You can see that BASIC customer with customer_id 2 has been upgraded to VIP customer.

The BEFORE UPDATE trigger is used to confirm BEFORE updating data and is a good guardian of the system’s business rules. Next, let’s look at the use of AFTER UPDATE in customer management systems.

MySQL Workbench operating MySQL/MariaDB database

4.AFTER INSERTUse of trigger

In this section we demonstrate AFTER UPDATE in action. AFTER UPDATE is mainly used to record logs. In an environment where multiple operators operate the management system, administrators need to set operation logs so that when faults occur, operators’ operations on the table can be viewed and traced back to the source.

Let’s start by creating a log trigger for operations on table SALES.

When the operator makes changes to table SALES, the operation record is written to the AUDIT_log table.

The trigger will monitor the user ID, total sales before update, total sales after update, operator ID, modification time and other information as logs into the AUDIT_log table.

Use the following command to create the log trigger:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;
Copy the code

When an operator updates a customer in table SALES, the trigger records the action in audit_log after the UPDATE. Including sales_id, changes in sales_amount value before and after modification.

The change in total sales is the key data for auditing, so it is recorded in audit_log. Use OLD to get the pre-update sales_amount value, and use NEW to get the post-update value.

In addition, we also record the operator information and operation time for modifying table SALES.

You can use SELECT USER() to check the account of the current USER, and use the NOW() statement to capture the current server date and time.

To test this trigger, let’s create an information record in table SALES:

Insert into sales(sales_id, customer_id, sales_amount) values('5'.'2'.'8000');
Copy the code
Output
Query OK, 1 row affected (0.00 sec)
Copy the code

Next, let’s update this record:

Update sales set sales_amount='9000' where sales_id='5';
Copy the code

You should see the following output:

Output
Rows matched: 1  Changed: 1  Warnings: 0
Copy the code

In theory, after we update table SALES, the trigger should trigger the operation that logs the changes we just made to the AUDIT_log table. Let’s use the following command to see if there are already records in the AUDIT_log table.

Select * from audit_log;
Copy the code

In the following table, the trigger updates the AUDIT_log table, which contains the old value before the Sales_AMOUNT update and the new value after the update.

At this point, the log auto-logging trigger made using AFTER UPDATE is complete.

In the next section, we’ll learn about DELETE related triggers.

Read: “how to view the MySQL database, tables, indexes, size? Find footprint the largest table”

5.BEFORE DELETEUse of trigger

The BEFORE DELETE trigger is called BEFORE the DELETE statement is executed.

These types of triggers are often used to enforce referential integrity on different related tables.

BEFORE DELETE is usually used to ensure that associated data is not deleted by mistake.

For example, the SALES table is associated with the CUSTOMERS table by customer_id. If the operator deletes a piece of data from the CUSTOMERS table, then some data in the SALES table loses its association clue.

To avoid this, we need to create a BEFORE DELETE trigger to prevent records from being deleted by mistake.

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'This customer has an associated sales record which cannot be deleted. ';
END IF//
DELIMITER ;
Copy the code

Now, we try to delete the customer with sales associated information:

Delete from customers where customer_id='2';
Copy the code

So, you should see the following output:

This trigger detects if Sales is related to the data in the Customers table that is about to be deleted, preventing data with associated information from being deleted by mistake.

Sometimes, however, we need to delete master data and then have the system automatically delete all other data associated with it. In this case, we will use the AFTER DELETE trigger.

DATETIME and TIMESTAMP in MySQL

6.AFTER DELETEUse of trigger

Moving on to AFTER DELETE, this trigger is activated once the record has been successfully deleted.

This trigger is also widely used in real-world scenarios. For example, in the upgrade and downgrade operation of the banking system, when the customer spends his or her account points, the trigger is activated, and the trigger can judge whether the remaining points meet the current level of the customer. If not, the upgrade operation will be performed automatically.

Another use of the AFTER DELETE trigger is to DELETE the data associated with the master table automatically AFTER the data in the master table is deleted.

Let’s see how this trigger is created:

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;
Copy the code

Next, let’s try this trigger. Delete the sales record whose customer_id is 2 from the sales record:

Delete from sales where customer_id='2';
Copy the code
Output
Query OK, 1 row affected (0.00 sec)
Copy the code

Next we check if the associated information in the following customers table is automatically deleted altogether:

Select * from customers where customer_id='2';
Copy the code

The command line returns the result of the Empty Set, and the relational information in the CUSTOMERS table is deleted as soon as we delete the information in the Sales table.

These are the six ways MySQL triggers can be used and the corresponding scenarios.

Read more: Top 10 MySQL/MariaDB Management Tools: Free vs. Paid

7. View the triggers

(1) Look directly at the trigger

When we want to see what triggers are in the database, we can use the following command:

SHOW TRIGGERS;
Copy the code

Followed by \G is the list of triggers in vertical order:

SHOW TRIGGERS \G
Copy the code

The triggers we just created are listed in this list.

(2) View the trigger information in the Triggers table

In MySQL Server, information for all triggers is stored in the Triggers table of the database Information_SCHEMA. All of which we can check with SELECT.

SELECT * FROM information_schema.triggers WHERE trigger_name= 'Trigger name';
Copy the code

Of course, you can view all of them without specifying the trigger name.

SELECT * FROM information_schema.triggers \G
Copy the code

MySQL/MariaDB MySQL/MariaDB MySQL/MariaDB MySQL/MariaDB

8. Delete triggers

Finally, let’s talk about deleting triggers. The delete command is also easy to Drop trigger trigger name.

Drop trigger [trigger name];Copy the code

For example, let’s delete the last trigger we just created:

Drop trigger delete_related_info;
Copy the code
Output
Query OK, 0 rows affected (0.00 sec)
Copy the code

Special note: we cannot modify triggers that have already been created. If you want to change it, you have to delete it first and then create it again.

How to store image BLOB data types in MySQL/MariaDB

9. To summarize

In this tutorial, we have shown six different forms of triggers before or after an INSERT, DELETE, or UPDATE execution, as well as six practical examples.

  • BEFORE INSERT: Checks whether the inserted data complies with the service logic. If the inserted data does not comply with the service logic, an error message is displayed.
  • AFTER INSERT: After an account is created in table A, the creation success information is automatically written to table B.
  • BEFORE UPDATE: Checks whether the updated data complies with the service logic. If the updated data does not comply with the service logic, an error message is displayed.
  • AFTER INSERT: Records operations in logs after data is updated
  • BEFORE DELETE: Check whether associated data exists before deleting data. If yes, stop the deletion.
  • AFTER DELETE: After information in table A is deleted, information associated with table A in table B is automatically deleted.

Then recommend cara cloud, as long as you know how to write MySQL, you can use Cara cloud to build your own data tools, such as data kanban, enterprise CRM, ERP, authority management background, account checking system, etc.

Cara Cloud is a new generation of low-code development tools, free of installation and deployment, with one-click access to common databases and apis including MySQL. According to their own workflow, customized development. No tedious front-end development, just simple drag and drop, can quickly build enterprise internal tools. Months of development work can be reduced to a few days after using Cara cloud. Welcome to try cara Cloud for free.

Carla cloud provides one-click access to common databases and apis

Cara Cloud can easily build data kanban or other internal tools based on the company’s workflow needs, and can be shared with the group of friends with one click.

! [5 minutes Carla cloud build enterprise internal tools (kalacloud.com/5400a60956e…).

The following figure shows the background of “coupon issuance and verification” built in 5 minutes by using Cara Cloud. It only needs simple drag and drop to quickly generate front-end components. As long as you can write SQL, you can build a set of convenient database tools. ** Welcome a free trial of Cara Cloud. **

I hope you found this tutorial helpful. For more MySQL tutorials, visit the Cara Cloud for more.

MySQL > MySQL > MySQL

  • How to remotely connect to MySQL database, Tencent cloud exnet connection tutorial
  • How to import and export data from MySQL/MariaDB, database files, Excel, and CSV
  • How to migrate MySQL database between two servers ali Cloud Tencent cloud migration case
  • MySQL > alter TABLE AUTO_INCREMENT MySQL > alter table AUTO_INCREMENT