Mysql 简明教程
MySQL - Before Update Trigger
MySQL 中的触发程序有两种类型：对于插入、删除和更新等各种 SQL 操作，可以使用 Before Triggers 和 After Triggers。正如我们在之前的章节中所学习的，After Update Trigger 在数据库表的某一行中的某个值更新后立即执行。在此，让我们进一步了解 BEFORE UPDATE 触发器。
Triggers in MySQL are of two types: Before Triggers and After Triggers for various SQL operations like insertion, deletion and update. As we have already learned in previous chapters, the After Update Trigger is executed immediately after a value is updated in a row of a database table. Here, let us learn more about BEFORE UPDATE trigger.
MySQL Before Update Trigger
Before Update Trigger 是由 MySQL 数据库支持的行级触发程序。它是一种特殊存储过程类型，在数据库表的某一行中的某个值更新之前自动执行。
The Before Update Trigger is a row-level trigger supported by the MySQL database. It is type of special stored procedure which is executed automatically before a value is updated in a row of a database table.
每当在数据库中执行 UPDATE 语句时，触发程序都会首先设置为关闭，然后是更新的值。
Whenever an UPDATE statement is executed in the database, the trigger is set to go off first followed by the updated value.
Syntax
以下为 MySQL 中创建 BEFORE UPDATE 触发程序的语法 −
Following is the syntax to create the BEFORE UPDATE trigger in MySQL −
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name FOR EACH ROW
BEGIN
-- trigger body
END;Example
让我们首先创建一个名为 USERS 的表，其中包含应用程序用户的详细信息。为此，请使用以下 CREATE TABLE 查询：
Let us first create a table named USERS containing the details of users of an application. Use the following CREATE TABLE query to do so −
CREATE TABLE USERS(
ID INT AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
AGE INT NOT NULL,
birthDATE VARCHAR(100),
PRIMARY KEY(ID)
);使用如下所示的常规 INSERT 语句将值插入到 USERS 表中：
Insert values into the USERS table using the regular INSERT statement as shown below −
INSERT INTO USERS (Name, Age, birthDATE) VALUES
('Sasha', 23, '24/06/1999');
('Alex', 21, '12/01/2001');USERS 表被创建如下：
The USERS table is created as follows −
Creating the trigger:
Creating the trigger:
使用以下 CREATE TRIGGER 语句，针对 USERS 表创建新触发程序 'before_update_trigger' 以使用 SQLSTATE 显示自定义错误，如下所示 −
Using the following CREATE TRIGGER statement, create a new trigger 'before_update_trigger' on the USERS table to display a customized error using SQLSTATE as follows −
DELIMITER //
CREATE TRIGGER before_update_trigger
BEFORE UPDATE ON USERS FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END //
DELIMITER ;使用常规 UPDATE 语句更新 USERS 表的值 −
Update values of the USERS table using the regular UPDATE statement −
UPDATE USERS SET AGE = -1 WHERE NAME = 'Sasha';