MySQL - AFTER UPDATE 更新后触发器

MySQL AFTER UPDATE Trigger 更新后触发器
使用客户端程序的更新后触发器

触发器简单来说就是对事件的响应。在 MySQL 中，触发器是一种驻留在系统目录中的特殊存储过程，每当发生事件时就会执行。它之所以被称为特殊存储过程，是因为它不需要像其他存储过程那样显式调用。每当触发所需事件时，触发器就会自动执行。

MySQL 更新后触发器

更新后触发器是 MySQL 数据库支持的行级触发器。顾名思义，更新后触发器会在数据库表的某一行更新值后立即执行。

行级触发器是一种在每次修改行时触发的触发器。简单来说，表中的每一个事务(例如插入、删除、更新)都会自动触发一个触发器。

在 MySQL 中定义更新后触发器后，每当数据库中执行 UPDATE 语句时，都会首先更新表的值，然后再执行触发器集。

语法

以下是在 MySQL 中创建 AFTER UPDATE 触发器的语法 -

CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name FOR EACH ROW
BEGIN
-- 触发器主体
END;

示例

首先，我们创建一个名为 USERS 的表，其中包含应用程序用户的详细信息。使用以下 CREATE TABLE 查询来执行此操作 -

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 INTO USERS (NAME, AGE, BIRTHDATE) VALUES
('Sasha', 23, '24/06/1999');
('Alex', 21, '12/01/2001');

USERS 表的创建方式如下:

ID	NAME	AGE	BIRTHDATE
1	Sasha	23	24/06/1999
2	Alex	21	12/01/2001

创建触发器:

使用以下 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';

输出

此查询的输出显示错误 -

ERROR 1644 (45000): Age Cannot be Negative

使用客户端程序的更新后触发器

我们也可以使用客户端程序在 MySQL 数据库中执行更新后触发器，而不是直接查询 SQL 语句。

语法

PHP NodeJS Java Python

要通过 PHP 程序执行更新后触发器，我们需要使用 mysqli 函数 query() 执行 CREATE TRIGGER 语句，如下所示 -

$sql = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END ";
$mysqli->query($sql);

要通过 JavaScript 程序执行更新后触发器，我们需要使用 mysql2 库的 query() 函数执行 CREATE TRIGGER 语句，如下所示 -

sql = `CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能为负数';
END IF;
END`;
con.query(sql);

要通过 Java 程序执行更新后触发器，我们需要使用 JDBC 函数 execute() 执行 CREATE TRIGGER 语句，如下所示 -

String sql = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能为负数';
END IF;
END";
statement.execute(sql);

要通过 Python 程序执行更新后触发器，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 CREATE TRIGGER 语句，如下所示 -

afterUpdate_trigger_query = 'CREATE TRIGGER {trigger_name}
AFTER UPDATE ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF
END'
cursorObj.execute(afterUpdate_trigger_query)

示例

以下是程序 -

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END ";
if($mysqli->query($sql)){
   printf("Trigger created successfully...!
");
}
$q = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
$result = $mysqli->query($q);
if ($result == true) {
   printf("Record updated successfully...!
");
}
if($mysqli->error){
   printf("Error message: " , $mysqli->error);
}
$mysqli->close();

输出

获得的输出如下 -

Trigger created successfully...!
PHP Fatal error:  Uncaught mysqli_sql_exception: Age Cannot be Negative

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//连接到 MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = `CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
 BEGIN
 IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
 END IF;
 END`;
 con.query(sql);
 console.log("After Update query executed successfully..!");
 sql = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
 con.query(sql);
 console.log("Table records: ")
 sql = "SELECT * FROM Sample";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

输出

生成的输出如下 -

Error: Age Cannot be Negative

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AfterUpdateTrigger {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM SAMPLE";
            rs = st.executeQuery(sql);
            System.out.println("Sample table records before update: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String birth_date = rs.getString("BIRTHDATE");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Birth_date: " + birth_date);
            }
            //lets create trigger on student table
            String sql1 = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
            END IF; 
            END";
            st.execute(sql1);
            System.out.println("Triggerd Created successfully...!");
            //let update the table records
            String sql3 = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
            st.execute(sql3);
            //let print SAMPLE table records
            String sql4 = "SELECT * FROM SAMPLE";
            rs = st.executeQuery(sql4);
            System.out.println("Sample table records after update: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String birth_date = rs.getString("BIRTHDATE");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Birth_date: " + birth_date);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

输出

获得的输出如下所示 -

Sample table records before update: 
Id: 1, Name: Sasha, Age: 23, Birth_date: 24/06/1999
Id: 2, Name: Alex, Age: 21, Birth_date: 12/01/2001
Triggerd Created successfully...!
java.sql.SQLException: Age Cannot be Negative

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
table_name = 'Sample'
trigger_name = 'after_update_trigger'
afterUpdate_trigger_query = f'''CREATE TRIGGER {trigger_name}
AFTER UPDATE ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END'''
cursorObj.execute(afterUpdate_trigger_query)
print(f"AFTER UPDATE Trigger '{trigger_name}' is created successfully.")
connection.commit()
# Update the "AGE" column
update_query = "UPDATE Sample SET AGE = -1 WHERE NAME = 'Sasha'"
cursorObj.execute(update_query)
print("Update query executed successfully.")
#关闭游标和连接
connection.commit()
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

AFTER UPDATE Trigger 'after_update_trigger' is created successfully.
Traceback (most recent call last):
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 633, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Age Cannot be Negative

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Lenovo\Desktop\untitled.py", line 29, in 
    cursorObj.execute(update_query)
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 330, in execute
    result = self._cnx.cmd_query(
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 77, in wrapper
    return method(cnx, *args, **kwargs)
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 641, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1644 (45000): Age Cannot be Negative

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - AFTER UPDATE 更新后触发器

MySQL 更新后触发器

语法

示例

输出

使用客户端程序的更新后触发器

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！