如何在Ubuntu 18.04上管理和使用MySQL数据库触发器

The author selected the the Apache Software Foundation to receive a donation as part of the Write for DOnations program.

作者选择了Apache软件基金会作为Write for DOnations计划的一部分来接受捐赠。

介绍 (Introduction)

In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation. The trigger code is associated with a table and is destroyed once a table is dropped. You can specify a trigger action time and set whether it will be activated before or after the defined database event.

在MySQL中 ， 触发器是用户定义SQL命令，该命令会在INSERT ， DELETE或UPDATE操作期间自动调用。 触发器代码与表相关联，一旦删除表，触发器代码就会被销毁。 您可以指定触发操作时间，并设置是在定义的数据库事件之前还是之后激活它。

Triggers have several advantages. For instance, you can use them to generate the value of a derived column during an INSERT statement. Another use case is enforcing referential integrity where you can use a trigger to save a record to multiple related tables. Other benefits include logging user actions to audit tables as well as live-copying data across different database schemas for redundancy purposes to prevent a single point of failure.

触发器具有几个优点。 例如，您可以使用它们在INSERT语句期间生成派生列的值。 另一个用例是强制引用完整性，您可以在其中使用触发器将记录保存到多个相关表中。 其他好处包括将用户操作记录到审核表中，以及跨不同数据库模式实时复制数据以实现冗余目的，以防止出现单点故障。

You can also use triggers to keep validation rules at the database level. This helps in sharing the data source across multiple applications without breaking the business logic. This greatly reduces round-trips to the database server, which therefore improves the response time of your applications. Since the database server executes triggers, they can take advantage of improved server resources such as RAM and CPU.

您还可以使用触发器将验证规则保留在数据库级别。 这有助于在不破坏业务逻辑的情况下跨多个应用程序共享数据源。 这大大减少了到数据库服务器的往返次数，从而缩短了应用程序的响应时间。 由于数据库服务器执行触发器，因此它们可以利用改进的服务器资源(例如RAM和CPU)。

In this tutorial, you’ll create, use, and delete different types of triggers on your MySQL database.

在本教程中，您将在MySQL数据库上创建，使用和删除不同类型的触发器。

先决条件 (Prerequisites)

Before you begin, make sure you have the following:

在开始之前，请确保您具有以下条件：

• One Ubuntu 18.04 server set up by following the Initial Server Setup with Ubuntu 18.04, including a sudo non-root user.

  通过对Ubuntu 18.04进行初始服务器设置来设置一台Ubuntu 18.04服务器，包括sudo非root用户。

• A MySQL database running on your server by following: How To Install MySQL on Ubuntu 18.04

  通过以下操作在服务器上运行MySQL数据库： 如何在Ubuntu 18.04上安装MySQL

• Root user account credentials for your MySQL database.
  MySQL数据库的根用户帐户凭据。

第1步-创建示例数据库 (Step 1 — Creating a Sample Database)

In this step, you’ll create a sample customer database with multiple tables for demonstrating how MySQL triggers work.

在此步骤中，您将创建一个包含多个表的示例客户数据库，以演示MySQL触发器的工作方式。

To understand more about MySQL queries read our Introduction to Queries in MySQL.

要了解有关MySQL查询的更多信息，请阅读我们MySQL查询简介 。

First, log in to your MySQL server as root:

首先，以root用户身份登录到MySQL服务器：

• mysql -u root -p
  mysql -u root -p

Enter your MySQL root password when prompted and hit ENTER to continue. When you see the mysql> prompt, run the following command to create a test_db database:

出现提示时输入您MySQL root密码，然后按ENTER继续。 当您看到mysql>提示符时，运行以下命令创建一个test_db数据库：

• Create database test_db;

  创建数据库test_db ;

   
   

    
    Output
   
   
Query OK, 1 row affected (0.00 sec)

Next, switch to the test_db with:

接下来，使用以下命令切换到test_db ：

• Use test_db;

  使用test_db ;

   
   

    
    Output
   
   
Database changed

You’ll start by creating a customers table. This table will hold the customers’ records including the customer_id, customer_name, and level. There will be two customer levels: BASIC and VIP.

您将从创建customers表开始。 该表将保存客户的记录，包括customer_id ， customer_name和level 。 将有两个客户级别： BASIC和VIP 。

• Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;
  创建表客户(customer_id BIGINT主键，customer_name VARCHAR(50)，级别VARCHAR(50))ENGINE = INNODB;

   
   

    
    Output
   
   
Query OK, 0 rows affected (0.01 sec)

Now, add a few records to the customers table. To do this, run the following commands one by one:

现在，将一些记录添加到customers表。 为此，请一一运行以下命令：

• Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
  将值('1'，'JOHN DOE'，'BASIC')插入客户(customer_id，customer_name，level)中;
• Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
  将值('2'，'MARY ROE'，'BASIC')插入客户(customer_id，customer_name，level)中;
• Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');
  将值('3'，'JOHN DOE'，'VIP')插入客户(customer_id，customer_name，level)中;

You’ll see the following output after running each of the INSERT commands:

运行每个INSERT命令后，您将看到以下输出：

   
   

    
    Output
   
   
Query OK, 1 row affected (0.01 sec)

To make sure that the sample records were inserted successfully, run the SELECT command:

要确保成功插入样本记录，请运行SELECT命令：

• Select * from customers;
  从客户中选择*；

   
   

    
    Output
   
   
+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

You’ll also create another table for holding related information about the customers account. The table will have a customer_id and status_notes fields.

您还将创建另一个表来保存有关customers帐户的相关信息。 该表将具有一个customer_id和status_notes字段。

Run the following command:

运行以下命令：

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

  创建表customer_status (customer_id BIGINT主键，status_notes VARCHAR(50))ENGINE = INNODB;

Next, you’ll create a sales table. This table will hold sales data related to the different customers through the customer_id column:

接下来，您将创建一个sales表。 该表将通过customer_id列保存与不同客户相关的销售数据：

• Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

  创建表销售 (sales_id BIGINT主键，customer_id BIGINT，sales_amount DOUBLE)ENGINE = INNODB;

   
   

    
    Output
   
   
Query OK, 0 rows affected (0.01 sec)

You’ll add sample data to the sales data in the coming steps while testing the triggers. Next, create an audit_log table to log updates made to the sales table when you implement the AFTER UPDATE trigger in Step 5:

在测试触发器时，您将在接下来的步骤中将示例数据添加到sales数据中。 接下来，创建一个audit_log表，以记录在步骤5中实施AFTER UPDATE触发器时对sales表所做的AFTER UPDATE ：

• 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;

  创建表audit_log (log_id BIGINT主键AUTO_INCREMENT，sales_id BIGINT，previous_amount DOUBLE，new_amount DOUBLE，updated_by VARCHAR(50)，updated_on DATETIME)ENGINE = INNODB;

   
   

    
    Output
   
   
Query OK, 0 rows affected (0.02 sec)

With the test_db database and the four tables in place, you’ll now move on to work with the different MySQL triggers in your database.

使用test_db数据库和四个表之后，现在您将继续使用数据库中的不同MySQL触发器。

第2步-创建插入前触发器 (Step 2 — Creating a Before Insert Trigger)

In this step, you’ll examine the syntax of a MySQL trigger before applying this logic to create a BEFORE INSERT trigger that validates the sales_amount field when data is inserted into the sales table.

在此步骤中，您将在应用此逻辑创建BEFORE INSERT触发器之前，先检查MySQL触发器的语法，该触发器将在将数据插入到sales表中时验证sales_amount字段。

The general syntax for creating a MySQL trigger is shown in the following example:

以下示例显示了创建MySQL触发器的常规语法：

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

The structure of the trigger includes:

触发器的结构包括：

DELIMITER //: The default MySQL delimiter is ;—it’s necessary to change it to something else in order for MySQL to treat the following lines as one command until it hits your custom delimiter. In this example, the delimiter is changed to // and then the ; delimiter is redefined at the end.

DELIMITER // ：默认MySQL分隔符为; —有必要将其更改为其他内容，以使MySQL将以下几行视为一条命令，直到它到达您的自定义分隔符为止。 在此示例中，定界符更改为// ，然后是; 在最后重新定义定界符。

[TRIGGER_NAME]: A trigger must have a name and this is where you include the value.

[TRIGGER_NAME] ：触发​​器必须具有一个名称，在其中包含值。

[TRIGGER TIME]: A trigger can be invoked during different timings. MySQL allows you to define if the trigger will initiate before or after a database operation.

[TRIGGER TIME] ：可以在不同的时间调用触发器。 MySQL允许您定义触发器是在数据库操作之前还是之后启动。

[TRIGGER EVENT]: Triggers are only invoked by INSERT, UPDATE, and DELETE operations. You can use any value here depending on what you want to achieve.

[TRIGGER EVENT] ：触发​​器仅由INSERT ， UPDATE和DELETE操作调用。 您可以在此处使用任何值，具体取决于要实现的目标。

[TABLE]: Any trigger that you create on your MySQL database must be associated with a table.

[TABLE] ：您在MySQL数据库上创建的任何触发器都必须与一个表关联。

FOR EACH ROW: This statement tells MySQL to execute the trigger code for every row that the trigger affects.

FOR EACH ROW ：此语句告诉MySQL为触发器影响的每一行执行触发器代码。

[TRIGGER BODY]: The code that is executed when the trigger is invoked is called a trigger body. This can be a single SQL statement or multiple commands. Note that if you are executing multiple SQL statements on the trigger body, you must wrap them between a BEGIN...END block.

[TRIGGER BODY] ：调用触发器时执行的代码称为触发器主体 。 这可以是单个SQL语句或多个命令。 请注意，如果要在触发器主体上执行多个SQL语句，则必须将它们包装在BEGIN...END块之间。

Note: When creating the trigger body, you can use the OLD and NEW keywords to access the old and new column values entered during an INSERT, UPDATE, and DELETE operation. In a DELETE trigger, only the OLD keyword can be used (which you’ll use in Step 4).

注意：创建触发器主体时，可以使用OLD和NEW关键字来访问在INSERT ， UPDATE和DELETE操作期间输入的旧列和新列值。 在DELETE触发器中，只能使用OLD关键字(您将在步骤4中使用)。

Now you’ll create your first BEFORE INSERT trigger. This trigger will be associated with the sales table and it will be invoked before a record is inserted to validate the sales_amount. The function of the trigger is to check if the sales_amount being inserted to the sales table is greater than 10000 and raise an error if this evaluates to true.

现在，您将创建第一个BEFORE INSERT触发器。 该触发器将与sales表关联，并在插入记录以验证sales_amount之前调用它。 触发器的功能是检查要插入到sales表中的sales_amount是否大于10000 ，如果此结果为true，则会引发错误。

Make sure you’re logged in to the MySQL server. Then, enter the following MySQL commands one by one:

确保您已登录到MySQL服务器。 然后，一一输入以下MySQL命令：

• DELIMITER //
  分隔符//
• CREATE TRIGGER validate_sales_amount
  创建触发器validate_sales_amount
• BEFORE INSERT
  插入之前
• ON sales
  特价出售
• FOR EACH ROW
  每行
• IF NEW.sales_amount>10000 THEN
  如果NEW.sales_amount> 10000 THEN
• SIGNAL SQLSTATE '45000'
  SIGNAL SQLSTATE'45000'
• SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
  SET MESSAGE_TEXT ='销售量已超过允许的10000。';
• END IF//
  万一//
• DELIMITER ;
  定界符;

You’re using the IF...THEN...END IF statement to evaluate if the amount being supplied during the INSERT statement is within your range. The trigger is able to extract the new sales_amount value being supplied by using the NEW keyword.

您正在使用IF...THEN...END IF语句来评估INSERT语句期间提供的数量是否在您的范围内。 触发器能够使用NEW关键字提取提供的新sales_amount值。

To raise a generic error message, you use the following lines to inform the user about the error:

要引发一般性错误消息，请使用以下几行将错误通知用户：

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

Next, insert a record with a sales_amount of 11000 to the sales table to check if the trigger will stop the operation:

接下来，在sales表中插入一个sales_amount为11000的记录，以检查触发器是否将停止操作：

• Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');
  插入sales(sales_id，customer_id，sales_amount)值('1'，'1'，'11000');

   
   

    
    Output
   
   
ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

This error shows that the trigger code is working as expected.

此错误表明触发代码正在按预期方式工作。

Now try a new record with a value of 7500 to check if the command will be successful:

现在尝试使用7500值的新记录来检查命令是否成功：

• Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');
  插入sales(sales_id，customer_id，sales_amount)值('1'，'1'，'7500');

Since the value is within the recommended range, you’ll see the following output:

由于该值在建议范围内，因此您将看到以下输出：

   
   

    
    Output
   
   
Query OK, 1 row affected (0.01 sec)

To confirm that the data was inserted run the following command:

要确认已插入数据，请运行以下命令：

• Select * from sales;
  从销售中选择*；

The output confirms that the data is in the table:

输出确认数据在表中：

   
   

    
    Output
   
   
+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

In this step you’ve tested triggers to validate data before insertion into a database.

在此步骤中，您已经测试了触发器以在插入数据库之前验证数据。

Next, you’ll work with the AFTER INSERT trigger to save related information into different tables.

接下来，您将使用AFTER INSERT触发器将相关信息保存到不同的表中。

步骤3 —创建插入后触发器 (Step 3 — Creating an After Insert Trigger)

AFTER INSERT triggers are executed when records are successfully inserted into a table. This functionality can be used to run other business-related logics automatically. For instance, in a bank application, an AFTER INSERT trigger can close a loan account when a customer finishes paying off the loan. The trigger can monitor all payments inserted to a transaction table and close the loan automatically once the loan balance is zero.

将记录成功插入表AFTER INSERT将执行AFTER INSERT触发器。 此功能可用于自动运行其他与业务相关的逻辑。 例如，在银行应用程序中，当客户完成还清贷款AFTER INSERT ， AFTER INSERT触发器可以关闭贷款帐户。 触发器可以监视插入到交易表中的所有付款，并在贷款余额为零时自动关闭贷款。

In this step, you’ll work with your customer_status table by using an AFTER INSERT trigger to enter related customer records.

在此步骤中，将通过使用AFTER INSERT触发器输入相关的客户记录来处理customer_status表。

To create the AFTER INSERT trigger, enter the following commands:

要创建AFTER INSERT触发器，请输入以下命令：

• DELIMITER //
  分隔符//
• CREATE TRIGGER customer_status_records
  创建触发器customer_status_records
• AFTER INSERT
  插入后
• ON customers
  在客户身上
• FOR EACH ROW
  每行
• Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
  插入到customer_status(customer_id，status_notes)VALUES(NEW.customer_id，'ACCOUNT OPENED SUCCESSFULLY')//
• DELIMITER ;
  定界符;

   
   

    
    Output
   
   
Query OK, 0 rows affected (0.00 sec)

Here you instruct MySQL to save another record to the customer_status table once a new customer record is inserted to the customers table.

在这里，您指示MySQL一旦将新的客户记录插入到customer_status表中，就将另一个记录保存到customers表中。

Now, insert a new record in the customers table to confirm your trigger code will be invoked:

现在，在customers表中插入一条新记录，以确认将调用您的触发代码：

• Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');
  将值('4'，'DAVID DOE'，'VIP')插入客户(customer_id，customer_name，level)中;

   
   

    
    Output
   
   
Query OK, 1 row affected (0.01 sec)

Since the record was inserted successfully, check that a new status record was inserted into the customer_status table:

由于记录已成功插入，因此请检查是否有新的状态记录已插入到customer_status表中：

• Select * from customer_status;
  从customer_status中选择*；

   
   

    
    Output
   
   
+-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

The output confirms that the trigger ran successfully.

输出确认触发器成功运行。

The AFTER INSERT trigger is useful in monitoring the lifecycle of a customer. In a production environment, customers’ accounts may undergo different stages such as account opening, suspension, and closing.

AFTER INSERT触发器可用于监视客户的生命周期。 在生产环境中，客户的帐户可能经历不同的阶段，例如帐户开设，暂停和关闭。

In the following steps you’ll work with UPDATE triggers.

在以下步骤中，您将使用UPDATE触发器。

步骤4 —创建更新前触发器 (Step 4 — Creating a Before Update Trigger)

A BEFORE UPDATE trigger is similar to the BEFORE INSERT trigger—the difference is when they are invoked. You can use the BEFORE UPDATE trigger to check a business logic before a record is updated. To test this, you’ll use the customers table in which you’ve inserted some data already.

BEFORE UPDATE触发器与BEFORE INSERT触发器类似，不同之处在于它们被调用的时间。 您可以使用BEFORE UPDATE触发器BEFORE UPDATE记录之前检查业务逻辑。 为了测试这一点，您将使用已经在其中插入了一些数据的customers表。

You have two levels for your customers in the database. In this example, once a customer account is upgraded to the VIP level, the account can not be downgraded to the BASIC level. To enforce such a rule, you will create a BEFORE UPDATE trigger that will execute before the UPDATE statement as shown following. If a database user tries to downgrade a customer to the BASIC level from the VIP level, a user-defined exception will be triggered.

在数据库中，您的客户有两个级别。 在此示例中，客户帐户一旦升级到VIP级别，就无法将该帐户降级到BASIC级别。 为了执行这样的规则，您将创建一个BEFORE UPDATE触发器，该触发器将在UPDATE语句之前执行，如下所示。 如果数据库用户试图将客户从VIP级别降级为BASIC级别，则将触发用户定义的异常。

Enter the following SQL commands one by one to create the BEFORE UPDATE trigger:

依次输入以下SQL命令以创建BEFORE UPDATE触发器：

• DELIMITER //
  分隔符//
• CREATE TRIGGER validate_customer_level
  创建触发器validate_customer_level
• BEFORE UPDATE
  更新之前
• ON customers
  在客户身上
• FOR EACH ROW
  每行
• IF OLD.level='VIP' THEN
  如果OLD.level ='VIP'然后
• SIGNAL SQLSTATE '45000'
  SIGNAL SQLSTATE'45000'
• SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
  SET MESSAGE_TEXT ='不能将VIP客户降级。';
• END IF //
  万一 //
• DELIMITER ;
  定界符;

You use the OLD keyword to capture the level that the user is supplying when running the UPDATE command. Again, you use the IF...THEN...END IF statement to signal a generic error statement to the user.

您可以使用OLD关键字来捕获用户在运行UPDATE命令时提供的级别。 同样，您使用IF...THEN...END IF语句向用户发出通用错误声明。

Next, run the following SQL command that tries to downgrade a customer account associated with the customer_id of 3:

接下来，运行以下SQL命令，尝试降级与customer_id为3关联的客户帐户：

• Update customers set level='BASIC' where customer_id='3';
  更新客户设置级别='BASIC'，其中customer_id ='3';

You’ll see the following output providing the SET MESSAGE_TEXT:

您将看到以下提供SET MESSAGE_TEXT输出：

   
   

    
    Output
   
   
ERROR 1644 (45000): A VIP customer can not be downgraded.

If you run the same command to a BASIC level customer, and try to upgrade the account to the VIP level, the command will execute successfully:

如果您对BASIC级别的客户运行相同的命令，并尝试将帐户升级到VIP级别，则该命令将成功执行：

• Update customers set level='VIP' where customer_id='1';
  更新客户设置级别='VIP'，其中customer_id ='1';

   
   

    
    Output
   
   
Rows matched: 1  Changed: 1  Warnings: 0

You’ve used the BEFORE UPDATE trigger to enforce a business rule. Now you’ll move on to use an AFTER UPDATE trigger for audit logging.

您已使用BEFORE UPDATE触发器来强制执行业务规则。 现在，您将继续使用AFTER UPDATE触发器进行审核日志记录。

步骤5 —创建更新后触发器 (Step 5 — Creating an After Update Trigger)

An AFTER UPDATE trigger is invoked once a database record is updated successfully. This behavior makes the trigger suitable for audit logging. In a multi-user environment, the administrator may want to view a history of users updating records in a particular table for audit purposes.

数据库记录成功更新AFTER UPDATE将调用AFTER UPDATE触发器。 此行为使触发器适合于审核日志记录。 在多用户环境中，出于审核目的，管理员可能希望查看用户更新特定表中记录的历史记录。

You’ll create a trigger that logs the update activity of the sales table. Our audit_log table will contain information about the MySQL users updating the sales table, the date of the update, and the new and old sales_amount values.

您将创建一个触发器来记录sales表的更新活动。 我们audit_log表将包含有关MySQL用户更新信息sales表，该date的更新，以及new和old sales_amount值。

To create the trigger, run the following SQL commands:

要创建触发器，请运行以下SQL命令：

• DELIMITER //
  分隔符//
• CREATE TRIGGER log_sales_updates
  创建触发器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() )//
  插入audit_log(sales_id，previous_amount，new_amount，updatedby，updated_on)VALUES(NEW.sales_id，OLD.sales_amount，NEW.sales_amount，(SELECT USER())，NOW())//
• DELIMITER ;
  定界符;

You insert a new record to the audit_log table. You use the NEW keyword to retrieve the value of the sales_id and the new sales_amount. Also, you use the OLD keyword to retrieve the previous sales_amount since you want to log both amounts for audit purposes.

您将新记录插入audit_log表。 您使用NEW关键字来检索sales_id和新的sales_amount 。 另外，由于要记录两个金额以进行审核，因此您可以使用OLD关键字来检索先前的sales_amount 。

The command SELECT USER() retrieves the current user performing the operation and the NOW() statement retrieves the value of the current date and time from the MySQL server.

命令SELECT USER()检索执行该操作的当前用户，而NOW()语句从MySQL服务器检索当前日期和时间的值。

Now if a user tries to update the value of any record in the sales table, the log_sales_updates trigger will insert a new record to the audit_log table.

现在，如果用户尝试更新sales表中任何记录的值，则log_sales_updates触发器将向audit_log表中插入新记录。

Let’s create a new sales record with a random sales_id of 5 and try to update it. First, insert the sales record with:

让我们创建一个新的销售记录，其sales_id为5并尝试对其进行更新。 首先，插入销售记录：

• Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');
  插入sales(sales_id，customer_id，sales_amount)值(“ 5”，“ 2”，“ 8000”)；

   
   

    
    Output
   
   
Query OK, 1 row affected (0.00 sec)

Next, update the record:

接下来，更新记录：

• Update sales set sales_amount='9000' where sales_id='5';
  更新销售集sales_amount ='9000'，其中sales_id ='5';

You’ll see the following output:

您将看到以下输出：

   
   

    
    Output
   
   
Rows matched: 1  Changed: 1  Warnings: 0

Now run the following command to verify if the AFTER UPDATE trigger was able to register a new record into the audit_log table:

现在运行以下命令来验证AFTER UPDATE触发器是否能够将新记录注册到audit_log表中：

• Select * from audit_log;
  从audit_log中选择*；

The trigger logged the update. Your output shows the previous sales_amount and new amount registered with the user that updated the records:

触发器记录了更新。 您的输出显示以前的sales_amount和向更新记录的用户注册的new amount ：

   
   

    
    Output
   
   
+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

You also have the date and time the update was performed, which are valuable for audit purposes.

您还具有执行更新的日期和时间，这对于审核目的很重要。

Next you’ll use the DELETE trigger to enforce referencing integrity at the database level.

接下来，您将使用DELETE触发器在数据库级别强制执行引用完整性。

步骤6 —创建删除前触发器 (Step 6 — Creating a Before Delete Trigger)

BEFORE DELETE triggers invoke before a DELETE statement executes on a table. These kinds of triggers are normally used to enforce referential integrity on different related tables. For example, each record on the sales table relates to a customer_id from the customers table. If a database user deleted a record from the customers table that has a related record in the sales table, you would have no way of knowing the customer associated with that record.

BEFORE DELETE表上执行DELETE语句BEFORE DELETE会触发调用。 这些触发器通常用于在不同的相关表上强制引用完整性。 例如，在每个记录sales表涉及customer_id从customers表。 如果数据库用户删除从记录customers已在相关记录表sales表，你将无法知道与该记录相关联的客户的方式。

To avoid this, you can create a BEFORE DELETE trigger to enforce your logic. Run the following SQL commands one by one:

为避免这种情况，您可以创建一个BEFORE DELETE触发器来执行您的逻辑。 一对一运行以下SQL命令：

• DELIMITER //
  分隔符//
• CREATE TRIGGER validate_related_records
  创建触发器validate_related_records
• BEFORE DELETE
  删除之前
• ON customers
  在客户身上
• FOR EACH ROW
  每行
• IF OLD.customer_id in (select customer_id from sales) THEN
  如果OLD.customer_id在(从销售中选择customer_id)中，则
• SIGNAL SQLSTATE '45000'
  SIGNAL SQLSTATE'45000'
• SET MESSAGE_TEXT = 'The customer has a related sales record.';
  SET MESSAGE_TEXT ='客户有相关的销售记录。';
• END IF//
  万一//
• DELIMITER ;
  定界符;

Now, try to delete a customer that has a related sales record:

现在，尝试删除具有相关销售记录的客户：

• Delete from customers where customer_id='2';
  从客户中删除，其中customer_id ='2';

As a result you’ll receive the following output:

结果，您将收到以下输出：

   
   

    
    Output
   
   
ERROR 1644 (45000): The customer has a related sales record.

The BEFORE DELETE trigger can prevent accidental deletion of related information in a database.

BEFORE DELETE触发器可以防止意外删除数据库中的相关信息。

However, in some situations, you may want to delete all the records associated with a particular record from the different related tables. In this situation you would use the AFTER DELETE trigger, which you’ll test in the next step.

但是，在某些情况下，您可能希望从不同的相关表中删除与特定记录关联的所有记录。 在这种情况下，您将使用AFTER DELETE触发器，将在下一步中对其进行测试。

第7步-创建删除后触发器 (Step 7 — Creating an After Delete Trigger)

AFTER DELETE triggers are activated once a record has been deleted successfully. An example of how you can use an AFTER DELETE trigger is a situation in which the discount level a particular customer receives is determined by the number of sales made during a defined period. If some of the customer’s records are deleted from the sales table, the customer discount level would need to be downgraded.

成功删除记录AFTER DELETE将激活AFTER DELETE触发器。 如何使用AFTER DELETE触发器的一个示例是，特定客户获得的折扣级别由定义期间内的销售数量决定。 如果从sales表中删除了一些客户记录，则需要降低客户折扣级别。

Another use of the AFTER DELETE trigger is deleting related information from another table once a record from a base table is deleted. For instance, you’ll set a trigger that deletes the customer record if the sales records with the related customer_id are deleted from the sales table. Run the following command to create your trigger:

AFTER DELETE触发器的另一种用法是，一旦从基本表中删除了一条记录，就从另一个表中删除了相关信息。 例如，如果从sales表中删除了具有相关的customer_id的销售记录，则将设置一个触发器以删除该客户记录。 运行以下命令来创建触发器：

• DELIMITER //
  分隔符//
• CREATE TRIGGER delete_related_info
  创建触发器delete_related_info
• AFTER DELETE
  删除后
• ON sales
  特价出售
• FOR EACH ROW
  每行
• Delete from customers where customer_id=OLD.customer_id;//
  从customer_id = OLD.customer_id; //的客户中删除
• DELIMITER ;
  定界符;

Next, run the following to delete all sales records associated with a customer_id of 2:

接下来，运行以下命令删除与customer_id为2关联的所有销售记录：

• Delete from sales where customer_id='2';
  从sales中删除，其中customer_id ='2';

   
   

    
    Output
   
   
Query OK, 1 row affected (0.00 sec)

Now check if there are records for the customer from the sales table:

现在检查sales表中是否有客户记录：

• Select * from customers where customer_id='2';
  从customer_id ='2'的客户中选择*；

You will receive an Empty Set output since the customer record associated with the customer_id of 2 was deleted by the trigger:

由于触发器已删除了与customer_id为2关联的客户记录，因此您将收到一个Empty Set输出：

   
   

    
    Output
   
   
Empty set (0.00 sec)

You’ve now used each of the different forms of triggers to perform specific functions. Next you will see how you can remove a trigger from the database if you no longer need it.

现在，您已经使用了每种不同形式的触发器来执行特定功能。 接下来，您将看到不再需要触发器时如何从数据库中删除触发器。

步骤8 —删除触发器 (Step 8 — Deleting Triggers)

Similarly to any other database object, you can delete triggers using the DROP command. The following is the syntax for deleting a trigger:

与任何其他数据库对象类似，您可以使用DROP命令删除触发器。 以下是删除触发器的语法：

Drop trigger [TRIGGER NAME];

For instance, to delete the last AFTER DELETE trigger that you created, run the following command:

例如，要删除您创建的最后一个AFTER DELETE触发器，请运行以下命令：

• Drop trigger delete_related_info;
  删除触发器delete_related_info;

   
   

    
    Output
   
   
Query OK, 0 rows affected (0.00 sec)

The need to delete triggers arises when you want to recreate its structure. In such a case, you can drop the trigger and redefine a new one with the different trigger commands.

当您要重新创建触发器结构时，需要删除触发器。 在这种情况下，您可以删除触发器并使用不同的触发器命令重新定义一个新的触发器。

结论 (Conclusion)

In this tutorial you’ve created, used, and deleted the different kinds of triggers from a MySQL database. Using an example customer-related database you’ve implemented triggers for different use cases such as data validation, business-logic application, audit logging, and enforcing referential integrity.

在本教程中，您已经从MySQL数据库中创建，使用和删除了各种触发器。 使用与客户相关的示例数据库，您已经为不同的用例实现了触发器，例如数据验证，业务逻辑应用程序，审计日志记录和强制引用完整性。

For further information on using your MySQL database, check out the following:

有关使用MySQL数据库的更多信息，请查看以下内容：

• How To Optimize MySQL with Query Cache on Ubuntu 18.04

  如何在Ubuntu 18.04上使用查询缓存优化MySQL

• How To Implement Pagination in MySQL with PHP on Ubuntu 18.04

  如何在Ubuntu 18.04上使用PHP在MySQL中实现分页

• How To Troubleshoot Issues in MySQL

  如何解决MySQL中的问题

翻译自: https://www.digitalocean.com/community/tutorials/how-to-manage-and-use-mysql-database-triggers-on-ubuntu-18-04