MySQL 8 教程

触发器的特点

• 自动执行：触发器在触发事件发生时自动执行，不需要手动调用
• 与表关联：触发器与特定的表关联，只有当该表发生触发事件时才会执行
• 事件驱动：触发器由特定的事件触发，如INSERT、UPDATE或DELETE操作
• 行级操作：触发器可以针对每条被修改的记录执行一次（FOR EACH ROW）
• 事务一致性：触发器的执行与触发它的语句在同一个事务中，可以通过ROLLBACK语句回滚

触发器的使用场景

• 数据验证：在数据插入或更新前验证数据的合法性
• 数据转换：在数据插入或更新时自动转换数据格式
• 审计跟踪：记录数据的变更历史，包括变更时间、变更人、变更内容等
• 级联更新：当一个表中的数据发生变更时，自动更新关联表中的数据
• 计算派生列值：根据其他列的值自动计算派生列的值
• 维护数据完整性：强制执行业务规则，确保数据的一致性和完整性

创建触发器的基本语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
begin
    -- 触发器要执行的SQL语句
end;

参数说明：

• trigger_name：触发器的名称
• BEFORE | AFTER：触发器的触发时间，表示在触发事件发生之前还是之后执行
• INSERT | UPDATE | DELETE：触发器的触发事件，表示哪种操作会触发触发器
• table_name：与触发器关联的表名
• FOR EACH ROW：表示触发器对每条被修改的记录执行一次
• begin ... end：包含触发器要执行的SQL语句

创建只有一个执行语句的触发器示例

-- 示例1：创建一个BEFORE INSERT触发器，在插入数据前验证年龄
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.birth_date = DATE(NEW.birth_date);  -- 确保birth_date是日期类型

-- 示例2：创建一个AFTER INSERT触发器，在插入数据后记录日志
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO employee_logs (employee_id, action, action_time) 
VALUES (NEW.employee_id, 'INSERT', NOW());

-- 示例3：创建一个BEFORE UPDATE触发器，在更新数据前记录旧值
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
INSERT INTO employee_audit (employee_id, old_salary, new_salary, changed_at) 
VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());

-- 示例4：创建一个AFTER DELETE触发器，在删除数据后记录删除的记录
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
INSERT INTO deleted_employees (employee_id, first_name, last_name, deleted_at) 
VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, NOW());

-- 示例5：创建一个BEFORE INSERT触发器，自动计算派生列的值
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.total_amount = NEW.quantity * NEW.unit_price;

-- 示例6：创建一个BEFORE UPDATE触发器，限制某些字段的修改
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
SET NEW.last_update = NOW();

-- 示例7：创建一个AFTER UPDATE触发器，在更新库存后检查库存水平
CREATE TRIGGER after_inventory_update
AFTER UPDATE ON inventory
FOR EACH ROW
INSERT INTO low_inventory_alerts (product_id, current_stock, alert_time) 
SELECT NEW.product_id, NEW.stock_quantity, NOW()
WHERE NEW.stock_quantity < 10;

-- 示例8：创建一个BEFORE DELETE触发器，防止删除重要记录
CREATE TRIGGER before_customer_delete
BEFORE DELETE ON customers
FOR EACH ROW
INSERT INTO deletion_attempts (customer_id, attempt_time, status) 
VALUES (OLD.customer_id, NOW(), 'BLOCKED');

-- 示例9：创建一个AFTER INSERT触发器，自动更新相关表的数据
CREATE TRIGGER after_sale_insert
AFTER INSERT ON sales
FOR EACH ROW
UPDATE products SET stock_quantity = stock_quantity - NEW.quantity 
WHERE product_id = NEW.product_id;

-- 示例10：创建一个BEFORE UPDATE触发器，记录数据修改的用户名
CREATE TRIGGER before_data_update
BEFORE UPDATE ON sensitive_data
FOR EACH ROW
SET NEW.modified_by = CURRENT_USER(),
    NEW.modified_at = NOW();

创建有多个执行语句的触发器示例

-- 示例1：创建一个包含多个执行语句的BEFORE INSERT触发器
-- 在MySQL命令行中，首先修改分隔符
delimiter //
CREATE TRIGGER before_employee_insert_complex
BEFORE INSERT ON employees
FOR EACH ROW
begin
    -- 验证年龄是否合法
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '员工年龄必须满18岁';
    END IF;
    
    -- 设置默认部门
    IF NEW.department_id IS NULL THEN
        SET NEW.department_id = 1;  -- 默认部门ID为1
    END IF;
    
    -- 自动生成员工编号
    SET NEW.employee_code = CONCAT('EMP-', LPAD(NEW.employee_id, 6, '0'));
    
    -- 记录创建信息
    SET NEW.created_at = NOW();
    SET NEW.created_by = CURRENT_USER();
end//
delimiter ;

-- 示例2：创建一个包含多个执行语句的AFTER UPDATE触发器
delimiter //
CREATE TRIGGER after_employee_update_complex
AFTER UPDATE ON employees
FOR EACH ROW
begin
    -- 记录工资变更
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_at, changed_by) 
        VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW(), CURRENT_USER());
    END IF;
    
    -- 记录部门变更
    IF OLD.department_id <> NEW.department_id THEN
        INSERT INTO department_transfer_logs (employee_id, from_department_id, to_department_id, transfer_date) 
        VALUES (OLD.employee_id, OLD.department_id, NEW.department_id, NOW());
        
        -- 更新部门员工数量
        UPDATE departments 
        SET employee_count = employee_count - 1 
        WHERE department_id = OLD.department_id;
        
        UPDATE departments 
        SET employee_count = employee_count + 1 
        WHERE department_id = NEW.department_id;
    END IF;
    
    -- 记录职位变更
    IF OLD.position <> NEW.position THEN
        INSERT INTO position_history (employee_id, old_position, new_position, changed_at) 
        VALUES (OLD.employee_id, OLD.position, NEW.position, NOW());
    END IF;
end//
delimiter ;

-- 示例3：创建一个包含多个执行语句的AFTER DELETE触发器
delimiter //
CREATE TRIGGER after_customer_delete_complex
AFTER DELETE ON customers
FOR EACH ROW
begin
    -- 记录删除的客户信息
    INSERT INTO deleted_customers (customer_id, first_name, last_name, email, phone, deleted_at, deleted_by) 
    VALUES (OLD.customer_id, OLD.first_name, OLD.last_name, OLD.email, OLD.phone, NOW(), CURRENT_USER());
    
    -- 标记相关订单为已取消
    UPDATE orders 
    SET status = 'CANCELED', 
        cancellation_reason = 'Customer deleted', 
        updated_at = NOW() 
    WHERE customer_id = OLD.customer_id 
    AND status NOT IN ('COMPLETED', 'CANCELED');
    
    -- 清理相关的购物车数据
    DELETE FROM shopping_carts 
    WHERE customer_id = OLD.customer_id;
    
    -- 清理相关的收藏夹数据
    DELETE FROM wishlists 
    WHERE customer_id = OLD.customer_id;
    
    -- 记录操作日志
    INSERT INTO audit_logs (entity_type, entity_id, action, performed_by, action_time) 
    VALUES ('CUSTOMER', OLD.customer_id, 'DELETE', CURRENT_USER(), NOW());
end//
delimiter ;

-- 示例4：创建一个包含多个执行语句的BEFORE UPDATE触发器，用于数据验证和转换
delimiter //
CREATE TRIGGER before_product_update_complex
BEFORE UPDATE ON products
FOR EACH ROW
begin
    -- 验证价格是否合法
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '产品价格必须大于0';
    END IF;
    
    -- 验证库存数量是否合法
    IF NEW.stock_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存数量不能为负数';
    END IF;
    
    -- 转换产品名称为大写
    SET NEW.product_name = UPPER(NEW.product_name);
    
    -- 自动设置更新时间和更新人
    SET NEW.updated_at = NOW();
    SET NEW.updated_by = CURRENT_USER();
    
    -- 如果产品状态变为禁用，标记相关的未处理订单
    IF OLD.status = 'ACTIVE' AND NEW.status = 'INACTIVE' THEN
        UPDATE orders 
        SET need_attention = 1, 
            updated_at = NOW() 
        WHERE product_id = NEW.product_id 
        AND status = 'PENDING';
    END IF;
end//
delimiter ;

-- 示例5：创建一个包含多个执行语句的AFTER INSERT触发器，用于数据同步
delimiter //
CREATE TRIGGER after_order_insert_complex
AFTER INSERT ON orders
FOR EACH ROW
begin
    -- 更新产品库存
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity, 
        updated_at = NOW() 
    WHERE product_id = NEW.product_id;
    
    -- 计算订单总金额
    DECLARE total_amount DECIMAL(10,2);
    SELECT SUM(quantity * unit_price) INTO total_amount 
    FROM order_items 
    WHERE order_id = NEW.order_id;
    
    -- 更新订单总金额
    UPDATE orders 
    SET total_amount = total_amount 
    WHERE order_id = NEW.order_id;
    
    -- 插入订单历史记录
    INSERT INTO order_history (order_id, status, changed_at) 
    VALUES (NEW.order_id, NEW.status, NOW());
    
    -- 记录销售统计
    INSERT INTO sales_statistics (product_id, sale_date, quantity_sold, revenue) 
    VALUES (NEW.product_id, CURDATE(), NEW.quantity, NEW.total_amount) 
    ON DUPLICATE KEY UPDATE 
        quantity_sold = quantity_sold + NEW.quantity, 
        revenue = revenue + NEW.total_amount;
    
    -- 检查是否需要补货
    IF (SELECT stock_quantity FROM products WHERE product_id = NEW.product_id) < 10 THEN
        INSERT INTO restock_alerts (product_id, current_stock, alert_date) 
        VALUES (NEW.product_id, (SELECT stock_quantity FROM products WHERE product_id = NEW.product_id), NOW())
        ON DUPLICATE KEY UPDATE 
            current_stock = (SELECT stock_quantity FROM products WHERE product_id = NEW.product_id), 
            alert_date = NOW();
    END IF;
end//
delimiter ;

使用SHOW TRIGGERS语句查看触发器信息

-- 查看当前数据库中的所有触发器
SHOW TRIGGERS;

-- 查看特定数据库中的触发器（需要指定数据库名）
SHOW TRIGGERS FROM database_name;

-- 查看与特定表关联的触发器
SHOW TRIGGERS LIKE 'table_name%';

在triggers表中查看触发器信息

-- 查看所有触发器的详细信息
SELECT * FROM information_schema.TRIGGERS;

-- 查看特定数据库中的触发器
SELECT * FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';

-- 查看与特定表关联的触发器
SELECT * FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name' 
AND EVENT_OBJECT_TABLE = 'table_name';

-- 查看特定名称的触发器
SELECT * FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = 'trigger_name';

-- 按触发事件类型查看触发器
SELECT * FROM information_schema.TRIGGERS
WHERE ACTION_TIMING = 'BEFORE';  -- 查看所有BEFORE触发器
-- 或
SELECT * FROM information_schema.TRIGGERS
WHERE ACTION_TIMING = 'AFTER';  -- 查看所有AFTER触发器

-- 按事件类型查看触发器
SELECT * FROM information_schema.TRIGGERS
WHERE EVENT_MANIPULATION = 'INSERT';  -- 查看所有INSERT触发器
-- 或
SELECT * FROM information_schema.TRIGGERS
WHERE EVENT_MANIPULATION = 'UPDATE';  -- 查看所有UPDATE触发器
-- 或
SELECT * FROM information_schema.TRIGGERS
WHERE EVENT_MANIPULATION = 'DELETE';  -- 查看所有DELETE触发器

-- 查看触发器的创建语句
SELECT TRIGGER_NAME, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';

-- 统计各类型触发器的数量
SELECT EVENT_MANIPULATION, ACTION_TIMING, COUNT(*) AS trigger_count
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name'
GROUP BY EVENT_MANIPULATION, ACTION_TIMING;

-- 查找包含特定代码的触发器
SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name'
AND ACTION_STATEMENT LIKE '%keyword%';

触发器中的OLD和NEW关键字

在MySQL触发器中，可以使用以下关键字来访问数据：

• OLD.column_name：在UPDATE或DELETE触发器中，引用操作前的列值
• NEW.column_name：在INSERT或UPDATE触发器中，引用操作后的列值

不同类型的触发器中OLD和NEW关键字的可用性：

触发器的使用示例

-- 示例1：使用INSERT触发器自动生成订单号
-- 首先创建一个BEFORE INSERT触发器
delimiter //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
begin
    -- 生成格式为ORD-YYYYMMDD-XXXX的订单号
    DECLARE order_number VARCHAR(20);
    SET order_number = CONCAT('ORD-', DATE_FORMAT(NOW(), '%Y%m%d'), '-', LPAD(FLOOR(1000 + (RAND() * 9000)), 4, '0'));
    SET NEW.order_number = order_number;
    
    -- 设置订单创建时间
    SET NEW.created_at = NOW();
    
    -- 设置订单状态为待处理
    SET NEW.status = 'PENDING';
end//
delimiter ;

-- 然后插入一条订单记录，触发器会自动生成订单号
INSERT INTO orders (customer_id, total_amount) VALUES (1, 299.99);

-- 查看插入的订单记录，包括自动生成的订单号
SELECT * FROM orders WHERE customer_id = 1 ORDER BY order_id DESC LIMIT 1;

-- 示例2：使用UPDATE触发器记录数据变更历史
-- 首先创建一个日志表
CREATE TABLE product_price_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(10,2) NOT NULL,
    new_price DECIMAL(10,2) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50) DEFAULT CURRENT_USER()
);

-- 然后创建一个BEFORE UPDATE触发器
delimiter //
CREATE TRIGGER before_product_price_update
BEFORE UPDATE ON products
FOR EACH ROW
begin
    -- 当价格发生变化时，记录变更历史
    IF OLD.price <> NEW.price THEN
        INSERT INTO product_price_history (product_id, old_price, new_price) 
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
end//
delimiter ;

-- 然后更新产品价格，触发器会自动记录变更历史
UPDATE products SET price = 59.99 WHERE product_id = 1;

-- 查看价格变更历史
SELECT * FROM product_price_history WHERE product_id = 1;

-- 示例3：使用DELETE触发器实现软删除
-- 首先创建一个BEFORE DELETE触发器，将删除操作转换为状态更新
delimiter //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
begin
    -- 阻止物理删除，改为更新状态为已删除
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = '物理删除用户被禁止，请使用UPDATE语句将用户状态设置为"DELETED"';
end//
delimiter ;

-- 尝试删除用户，会触发触发器并报错
DELETE FROM users WHERE user_id = 1;

-- 正确的做法是更新用户状态
UPDATE users SET status = 'DELETED', deleted_at = NOW() WHERE user_id = 1;

-- 示例4：使用触发器实现数据验证和约束
-- 创建一个BEFORE INSERT触发器，验证用户年龄
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 13 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户年龄必须满13岁';
    END IF;
    
    IF NEW.email NOT LIKE '%@%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请输入有效的电子邮件地址';
    END IF;
END;

-- 尝试插入不符合验证条件的用户数据
INSERT INTO users (username, email, age) VALUES ('younguser', 'invalid-email', 12);

-- 插入符合验证条件的用户数据
INSERT INTO users (username, email, age) VALUES ('validuser', 'valid@email.com', 25);

-- 示例5：使用触发器实现数据同步
-- 创建一个AFTER UPDATE触发器，在用户信息更新后同步更新相关表的数据
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    -- 同步更新用户配置表中的用户名
    UPDATE user_settings SET username = NEW.username WHERE user_id = NEW.user_id;
    
    -- 同步更新用户日志表中的用户名
    UPDATE user_logs SET username = NEW.username WHERE user_id = NEW.user_id;
    
    -- 同步更新用户评论表中的用户名
    UPDATE comments SET username = NEW.username WHERE user_id = NEW.user_id;
END;

删除触发器的基本语法

DROP TRIGGER [IF EXISTS] [database_name.]trigger_name;

参数说明：

• IF EXISTS：可选，如果指定的触发器不存在，不会报错
• database_name：可选，触发器所在的数据库名
• trigger_name：要删除的触发器的名称

删除触发器的示例

-- 示例1：删除特定的触发器
DROP TRIGGER before_employee_insert;

-- 示例2：使用IF EXISTS选项删除触发器，如果触发器不存在不会报错
DROP TRIGGER IF EXISTS after_order_update;

-- 示例3：删除特定数据库中的触发器
DROP TRIGGER IF EXISTS database_name.before_product_delete;

-- 示例4：删除与特定表关联的所有触发器
-- 首先查看与特定表关联的所有触发器
SELECT TRIGGER_NAME FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name' 
AND EVENT_OBJECT_TABLE = 'table_name';

-- 然后逐个删除这些触发器
DROP TRIGGER IF EXISTS database_name.trigger1;
DROP TRIGGER IF EXISTS database_name.trigger2;
DROP TRIGGER IF EXISTS database_name.trigger3;

-- 示例5：删除数据库中的所有触发器
-- 注意：在生产环境中，请谨慎执行此操作
-- 首先查看数据库中的所有触发器
SELECT TRIGGER_NAME FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';

-- 然后逐个删除这些触发器
-- （实际操作中，可以使用脚本生成并执行删除语句）

在PHP中操作触发器

<?php
// 连接到MySQL数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 创建触发器
function createTrigger($conn, $trigger_sql) {
    if ($conn->query($trigger_sql) === TRUE) {
        echo "触发器创建成功\n";
        return true;
    } else {
        echo "触发器创建失败: " . $conn->error . "\n";
        return false;
    }
}

// 示例：创建一个BEFORE INSERT触发器
$trigger_sql = "CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户年龄必须满18岁';
    END IF;
    SET NEW.created_at = NOW();
END";

createTrigger($conn, $trigger_sql);

// 查看触发器
function viewTriggers($conn, $table_name = null) {
    if ($table_name) {
        $sql = "SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = ? AND EVENT_OBJECT_TABLE = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $dbname, $table_name);
    } else {
        $sql = "SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("s", $dbname);
    }
    
    $stmt->execute();
    $result = $stmt->get_result();
    
    $triggers = array();
    while ($row = $result->fetch_assoc()) {
        $triggers[] = $row;
    }
    
    $stmt->close();
    return $triggers;
}

// 示例：查看特定表的触发器
$table_triggers = viewTriggers($conn, "users");
echo "\n用户表的触发器：\n";
foreach ($table_triggers as $trigger) {
    echo "触发器名称: " . $trigger["TRIGGER_NAME"] . "\n";
    echo "触发时间: " . $trigger["ACTION_TIMING"] . "\n";
    echo "触发事件: " . $trigger["EVENT_MANIPULATION"] . "\n";
    echo "--------------------------------\n";
}

// 删除触发器
function dropTrigger($conn, $trigger_name) {
    $sql = "DROP TRIGGER IF EXISTS $trigger_name";
    if ($conn->query($sql) === TRUE) {
        echo "触发器 '$trigger_name' 删除成功\n";
        return true;
    } else {
        echo "触发器 '$trigger_name' 删除失败: " . $conn->error . "\n";
        return false;
    }
}

// 示例：删除特定的触发器
// dropTrigger($conn, "before_user_insert");

// 关闭连接
$conn->close();
?>

在Python中操作触发器

# 导入MySQL连接器
import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="myDB"
)

# 创建游标对象
cursor = db.cursor()

# 创建触发器
def create_trigger(cursor, trigger_sql):
    try:
        cursor.execute(trigger_sql)
        print("触发器创建成功")
        return True
    except mysql.connector.Error as error:
        print(f"触发器创建失败: {error}")
        return False

# 示例：创建一个AFTER INSERT触发器
# 注意：在Python中，需要将触发器定义中的分号转义或使用三引号
create_trigger_sql = """CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_logs (order_id, action, action_time) 
    VALUES (NEW.order_id, 'INSERT', NOW());
END"""

create_trigger(cursor, create_trigger_sql)

# 查看触发器
def view_triggers(cursor, table_name=None):
    try:
        if table_name:
            query = """SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, ACTION_STATEMENT 
                      FROM information_schema.TRIGGERS 
                      WHERE TRIGGER_SCHEMA = DATABASE() 
                      AND EVENT_OBJECT_TABLE = %s"""
            cursor.execute(query, (table_name,))
        else:
            query = """SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, ACTION_STATEMENT 
                      FROM information_schema.TRIGGERS 
                      WHERE TRIGGER_SCHEMA = DATABASE()"""
            cursor.execute(query)
        
        # 获取查询结果
        result = cursor.fetchall()
        
        triggers = []
        for row in result:
            trigger = {
                "trigger_name": row[0],
                "action_timing": row[1],
                "event_manipulation": row[2],
                "action_statement": row[3]
            }
            triggers.append(trigger)
        
        return triggers
    except mysql.connector.Error as error:
        print(f"查询触发器失败: {error}")
        return []

# 示例：查看特定表的触发器
order_triggers = view_triggers(cursor, "orders")
print("\n订单表的触发器：")
for trigger in order_triggers:
    print(f"触发器名称: {trigger['trigger_name']}")
    print(f"触发时间: {trigger['action_timing']}")
    print(f"触发事件: {trigger['event_manipulation']}")
    print("--------------------------------")

# 删除触发器
def drop_trigger(cursor, trigger_name):
    try:
        query = f"DROP TRIGGER IF EXISTS {trigger_name}"
        cursor.execute(query)
        print(f"触发器 '{trigger_name}' 删除成功")
        return True
    except mysql.connector.Error as error:
        print(f"触发器 '{trigger_name}' 删除失败: {error}")
        return False

# 示例：删除特定的触发器
# drop_trigger(cursor, "after_order_insert")

# 关闭游标和连接
cursor.close()
db.close()