Mysql 简明教程
MySQL - After Update Trigger
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
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.
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
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
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 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');The USERS table is created as follows −
Creating the trigger:
使用以下 CREATE TRIGGER 语句，在 USERS 表上创建一个新触发器 after_update_trigger 以使用 SQLSTATE 显示以下自定义错误 -
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 SAMPLE SET AGE = -1 WHERE NAME = 'Sasha';