MySQL - 更新后触发器



触发器简单定义为对事件的响应。在 MySQL 中,触发器是一种驻留在系统目录中的特殊存储过程,并在执行事件时执行。它被称为特殊存储过程,因为它不需要像其他存储过程那样显式调用。触发器在所需事件触发时自动执行。

MySQL 更新后触发器

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

行级触发器是一种每当修改一行时就会触发的触发器。简单来说,对于在表中进行的每个事务(如插入、删除、更新),都会有一个触发器自动执行。

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

语法

以下是创建 MySQL 中的更新后触发器的语法:

CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW BEGIN -- trigger body 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 姓名 年龄 出生日期
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

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

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

语法

要通过 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 = 'Age Cannot be Negative'; 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 = 'Age Cannot be Negative'; 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)

示例

以下是程序:

$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...!\n"); } $q = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'"; $result = $mysqli->query($q); if ($result == true) { printf("Record updated successfully...!\n"); } 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" }); //Connecting to 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://#: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 # Establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) # Creating a cursor object 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.") # close the cursor and connection 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    
广告