笙亿网络策划

MySQL After Delete Trigger怎么创建和使用？

约 28 分钟读完 6 阅
文章导读
上一个 测验 下一个 一般来说，Trigger 被定义为对事件的响应。在 MySQL 中，trigger 被称作特殊的存储过程，因为它不像其他存储过程那样需要显式调用。只要期望的事件触发，trigger 就会自动执行。Trigger 分为两种类型：Before Trigge
📋 目录
  1. A MySQL 删除后触发器
  2. B 使用客户端程序的 After Delete Trigger
A A

MySQL - 删除后触发器

目录
  • MySQL 删除后触发器
  • 使用客户端程序的删除后触发器
上一个
测验
下一个

一般来说，Trigger 被定义为对事件的响应。在 MySQL 中，trigger 被称作特殊的存储过程，因为它不像其他存储过程那样需要显式调用。只要期望的事件触发，trigger 就会自动执行。Trigger 分为两种类型：Before Trigger 和 After Trigger。

这些 trigger 可以是对表上的插入操作、更新操作或删除操作的响应。因此，每当执行 INSERT、UPDATE 或 DELETE 语句时，这些特殊的存储过程就会响应。

MySQL 删除后触发器

删除后触发器是 MySQL 数据库支持的行级 trigger。此 trigger 在数据库表的一行中删除值后立即执行。

行级 trigger 是一种每次修改行时都会执行的 trigger。对于表中进行的每笔事务（例如插入、删除、更新操作），都会自动触发一个 trigger。

当在数据库中执行 DELETE 语句时，首先执行 trigger，然后从表中删除指定值。

语法

以下是在 MySQL 中创建 AFTER DELETE trigger 的语法 −

CREATE TRIGGER trigger_name
AFTER DELETE ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

示例

在本示例中，我们创建一个名为 'CUSTOMERS' 的表，以演示 AFTER DELETE trigger，使用以下查询 −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

使用以下 INSERT 语句向该表插入值 −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

创建另一个表：

现在，让我们创建一个空表，用于在从主表 'CUSTOMERS' 中删除后存储所有前客户 −

CREATE TABLE OLD_CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

使用以下 CREATE TRIGGER 语句，在 CUSTOMERS 表上创建一个名为 'after_delete_trigger' 的新 trigger，从 CUSTOMERS 表中删除客户详情并将其插入到另一个表 "OLD_CUSTOMERS" 中 −

DELIMITER //
CREATE TRIGGER after_delete_trigger 
AFTER DELETE ON CUSTOMERS 
FOR EACH ROW
BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES
 (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //
DELIMITER ;

使用以下常规 DELETE 语句从 CUSTOMERS 表中删除 'old' 客户的详情 −

DELETE FROM CUSTOMERS WHERE ID = 3;

验证

为了验证详情是否已从 CUSTOMERS 表中删除并添加到 OLD_CUSTOMERS 表中，让我们使用 SELECT 查询检索两者的结果集。

CUSTOMERS 表中的记录如下 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

OLD_CUSTOMERS 表中的记录如下 −

ID NAME AGE ADDRESS SALARY
3 Kaushik 23 Kota 2000.00

如上表所示，数据已从 CUSTOMERS 表中删除并添加到 OLD_CUSTOMERS 表中。然而，在应用层面上看不到的唯一区别是，trigger 在删除完成后执行，与 BEFORE DELETE trigger 相反。

使用客户端程序的 After Delete Trigger

我们也可以使用客户端程序来执行 After Delete trigger 语句，而不是使用 SQL 查询。

语法

PHP NodeJS Java Python

通过 PHP 程序执行 After Delete Trigger 时，我们需要使用 mysqli 函数 query() 来查询 CREATE TRIGGER 语句，如下所示 −

$sql = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
$mysqli->query($sql);

通过 JavaScript 程序执行 After Delete Trigger 时，我们需要使用 mysql2 library 的 query() 函数来查询 CREATE TRIGGER 语句，如下所示 −

sql = `CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END`;
con.query(sql);

通过 Java 程序执行 After Delete Trigger 时，我们需要使用 JDBC 函数 execute() 来查询 CREATE TRIGGER 语句，如下所示 −

String sql = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); 
END";   
statement.execute(sql);

通过 Python 程序执行 After Delete Trigger 时，我们需要使用 MySQL Connector/Python 的 execute() 函数来查询 CREATE TRIGGER 语句，如下所示 −

afterDelete_trigger_query = 'CREATE TRIGGER {trigger_name}
AFTER DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END'    
cursorObj.execute(afterDelete_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_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
if ($mysqli->query($sql)) {
    printf("Trigger created successfully...!\n");
}
$q = "DELETE FROM CUSTOMERS WHERE ID = 3";
$result = $mysqli->query($q);
if ($result == true) {
    printf("Delete query executed successfully ...!\n");
}
$q1 = "SELECT * FROM CUSTOMERS";
$res1 = $mysqli->query($q1);
if ($res1->num_rows > 0) {
    printf("SELECT * FROM CUSTOMERS(verification): \n");
    while ($r1 = $res1->fetch_assoc()) {
        printf(
            "Id %d, Name: %s, Age: %d, Address %s, Salary %f",
            $r1['ID'],
            $r1["NAME"],
            $r1['AGE'],
            $r1["ADDRESS"],
            $r1["SALARY"],
        );
        printf("\n");
    }
}
$q2 = "SELECT * FROM OLD_CUSTOMERS";
$res2 = $mysqli->query($q2);
if ($res2->num_rows > 0) {
    printf("SELECT * FROM OLD_CUSTOMER(verification): \n");
    while ($r1 = $res2->fetch_assoc()) {
        printf(
            "Id %d, Name: %s, Age: %d, Address %s, Salary %f",
            $r1['ID'],
            $r1["NAME"],
            $r1['AGE'],
            $r1["ADDRESS"],
            $r1["SALARY"],
        );
        printf("\n");
    }
}
if ($mysqli->error) {
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

输出

得到的输出如下所示 −

Trigger created successfully...!
Delete query executed successfully ...!
SELECT * FROM CUSTOMERS(verification):
Id 1, Name: Ramesh, Age: 32, Address Ahmedabad, Salary 2000.000000
Id 2, Name: Khilan, Age: 25, Address Delhi, Salary 1500.000000
Id 4, Name: Chaitali, Age: 25, Address Mumbai, Salary 6500.000000
Id 5, Name: Hardik, Age: 27, Address Bhopal, Salary 8500.000000
Id 6, Name: Komal, Age: 22, Address MP, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address Indore, Salary 10000.000000
SELECT * FROM OLD_CUSTOMER(verification):
Id 3, Name: Kaushik, Age: 23, Address Kota, Salary 2000.000000         

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_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
 BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
 END`;
 con.query(sql);
 console.log("After delete query executed successfully..!");
 sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
 con.query(sql);
 console.log("Customers table records: ")
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 console.log('---------------------------------');
 console.log('OLD_CUSTOMERS table records: ')
 });
 sql = "SELECT * FROM OLD_CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

输出

产生的输出如下所示 −

After delete query executed successfully..!
Customers table records:
[
  {
    ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: 'Ahmedabad',
    SALARY: '2000.00'
  },
  {
    ID: 2,
    NAME: 'Khilan',
    AGE: 25,
    ADDRESS: 'Delhi',
    SALARY: '1500.00'
  },
  {
    ID: 4,
    NAME: 'Chaitali',
    AGE: 25,
    ADDRESS: 'Mumbai',
    SALARY: '6500.00'
  },
  {
    ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: 'Bhopal',
    SALARY: '8500.00'
  },
  { ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' },
  {
    ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: 'Indore',
    SALARY: '10000.00'
  }
]
---------------------------------
OLD_CUSTOMERS table records:
[
  {
    ID: 3,
    NAME: 'Kaushik',
    AGE: 23,
    ADDRESS: 'Kota',
    SALARY: '2000.00'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AfterDeleteTrigger {
    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...!");
             //create table
            String sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql);
            System.out.println("Customers table created successfully...!");
            //lets insert some records into customers table
            String sql1 = "INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ), (2, 'Khilan', 25, 'Delhi', 1500.00 ), (3, 'Kaushik', 23, 'Kota', 2000.00 ), (4, 'Chaitali', 25, 'Mumbai', 6500.00 ), (5, 'Hardik', 27, 'Bhopal', 8500.00 ), (6, 'Komal', 22, 'MP', 4500.00 ), (7, 'Muffy', 24, 'Indore', 10000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Customers table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //let create one more table named Old_customers
            String sql3 = "CREATE TABLE OLD_CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql3);
            System.out.println("OLD_CUSTOMERS table created successully...!");
            //print the records
            String sql4 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("OLD_CUSTOMERS table records before delete trigger: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //lets create trigger on student table
            String sql5 = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
            END";
            st.execute(sql5);
            System.out.println("Triggerd Created successfully...!");
            //lets delete one record from customers table;
            String d_sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
            st.execute(d_sql);
            System.out.println("Record with id = 3 deleted successfully....!");
            //let print OLD_CUSTOMERS table records
            String sql6 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("OLD_CUSTOMERS records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

输出

得到的输出如下所示 −

Customers table created successfully...!
Records inserted successfully...!
Customers table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: Kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
OLD_CUSTOMERS table created successully...!
OLD_CUSTOMERS table records before delete trigger: 
Triggerd Created successfully...!
Record with id = 3 deleted successfully....!
OLD_CUSTOMERS records: 
Id: 3, Name: Kaushik, Age: 23, Address: Kota, Salary: 2000.00   

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 = 'Customers'
another_table = 'OLD_CUSTOMERS'
trigger_name = 'after_delete_trigger'
afterDelete_trigger_query = f'''
CREATE TRIGGER {trigger_name}
AFTER DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END
'''
cursorObj.execute(afterDelete_trigger_query)
print(f"AFTER DELETE Trigger '{trigger_name}' is created successfully.")
connection.commit()
# Delete details of old customer
delete_query = "DELETE FROM Customers WHERE ID = 3;"
cursorObj.execute(delete_query)
print("Delete query executed successfully.")
# close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

输出

上述代码的输出如下所示 −

AFTER DELETE Trigger 'after_delete_trigger' is created successfully.
Delete query executed successfully.