Mysql 简明教程
MySQL - After Update Trigger
Trigger 只是对一个事件的响应的定义。在 MySQL 中，触发器是一个特殊存储过程，驻留在系统目录中，并且在事件执行时执行。它被称为特殊存储过程，因为它不需要像其他存储过程那样显式调用。触发器在所需事件触发时自动执行。
A Trigger is simply defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, and is executed whenever an event is performed. It is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired.
MySQL After Update Trigger
After Update Trigger 是 MySQL 数据库支持的行级触发器。顾名思义，After Update Trigger 在数据库表的一行中的值更新后立即执行。
The After Update Trigger is a row-level trigger supported by the MySQL database. As its name suggests, the After Update Trigger is executed right after a value is updated in a row of a database table.
一旦在 MySQL 中定义了 After Update 触发器，每当在数据库中执行 UPDATE 语句时，表的值都会先更新，然后执行触发器设置。
Once the After Update trigger is defined in MySQL, whenever an UPDATE statement is executed in the database, the value of a table is updated first followed by execution of the trigger set.
Syntax
以下是在 MySQL 中创建 AFTER UPDATE 触发器的语法：
Following is the syntax to create the AFTER UPDATE trigger in MySQL −
CREATE TRIGGER trigger_name
AFTER 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 表上创建一个新触发器 after_update_trigger 以使用 SQLSTATE 显示以下自定义错误 -
Using the following CREATE TRIGGER statement, create a new trigger 'after_update_trigger' on the USERS table to display a customized error using SQLSTATE as follows −
DELIMITER //
CREATE TRIGGER after_update_trigger AFTER 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 语句（如下所示）更新 SAMPLE 表的值 -
Update values of the SAMPLE table using the regular UPDATE statement as shown below −
UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha';