帮我解决 禁止删除表数据，报警推送

Question

捕获 trigger before delete on table_name for each row

```sql
# 捕获  trigger  before delete on table_name  for each row
# 重点是 ，我要捕获表里面变化的数据信息 插入到我的 记录审核表 audit_table 里面, 
我用 “before” 主要目的是解决 不能让任何人 删除表里面数据，同时我要知道 谁在操作删除动作，发出报警信息。

```

Answer 1

引自免费微信小程序：皆我百晓生

为了实现你的需求，你可以创建一个MySQL触发器（trigger），该触发器将在目标表执行删除操作之前执行，然后获取将要被删除的数据的信息并将其插入到审核表（audit_table）中。同时，你可以利用MySQL的事件调度器（Event Scheduler）来发送报警信息。以下是具体的实现步骤和代码示例：

步骤 1：创建audit_table表结构

首先，你需要一个用于记录审核信息的表。这个表应该包含足够的信息来记录删除操作的情况，如操作时间、操作人、被删除的数据等。以下是一个简单的audit_table表结构示例：

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    operation_type VARCHAR(50) NOT NULL,  -- 操作类型，如'DELETE'
    operator_id INT,                     -- 操作人的ID
    table_name VARCHAR(255),            -- 被操作的表名
    old_data TEXT,                      -- 被删除的数据（可以存储JSON格式或其他格式）
    FOREIGN KEY (operator_id) REFERENCES users(id)  -- 假设有一个users表存储用户信息
);

步骤 2：创建触发器

接下来，创建一个触发器，该触发器将在目标表执行删除操作之前执行，并将相关信息插入到audit_table中。同时，你可以利用MySQL的SESSION变量来传递信息。以下是触发器的示例代码：

DELIMITER //
CREATE TRIGGER before_delete_table_name BEFORE DELETE ON table_name FOR EACH ROW 
BEGIN
    -- 获取当前登录用户ID（假设存在一个users表存储用户信息）
    DECLARE operator_id INT;
    SET operator_id = (SELECT id FROM users WHERE username = CURRENT_USER());
    
    -- 将要删除的数据信息插入到audit_table中
    INSERT INTO audit_table (operation_time, operation_type, operator_id, table_name, old_data) 
    VALUES (NOW(), 'DELETE', operator_id, 'table_name', OLD.column1, OLD.column2, ...); -- 根据你的表结构调整列名
END; //
DELIMITER ;

注意：在上面的代码中，你需要根据你的表结构调整列名（column1, column2, ...），并确保替换table_name为你要保护的实际表名。同时确保在真实环境中正确地获取用户ID的方式与上述代码相匹配。如果你的系统中没有users表或者其他获取用户ID的方式，你可能需要调整这部分的代码。此外，OLD关键字用于访问被删除的记录的值。如果你的表有多个列，你需要列出所有需要记录的列。如果你的表结构复杂或者有很多列，建议以JSON格式存储被删除的数据（old_data列）。你可以使用MySQL内置的JSON函数进行转换和操作。这将帮助你更方便地管理和查询这些数据。在MySQL 5.7及更高版本中支持JSON数据类型和相关函数。你可以在插入语句中使用MySQL的JSON函数将整行数据转换为JSON格式存储。这将在old_data列中以文本形式保存整行数据的快照。下面是一个修改后的示例：首先在audit_table表中增加用于存储JSON数据的字段定义：sql CREATE TABLE audit_table ( id INT AUTO_INCREMENT PRIMARY KEY, operation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, operation_type VARCHAR(50) NOT NULL, operator_id INT, table_name VARCHAR(255), old_data JSON, FOREIGN KEY (operator_id) REFERENCES users(id)); 然后修改插入语句使用JSON函数将旧数据转换为JSON格式存储： sql INSERT INTO audit_table (operation_time, operation_type, operator_id, table_name, old_data) VALUES (NOW(), 'DELETE', operator_id, 'table_name', JSON_OBJECT('column1', OLD.column1, 'column2', OLD.column2, ...)); 注意这里的JSON_OBJECT函数会创建一个JSON对象，每个参数是键值对中的一个键和对应的值。你可以根据实际需要添加更多的键值对到函数中，以便完整地记录数据行的情况。通过这种方式，即使表结构发生变化或者数据量很大，你也能方便地在audit表中查询和管理历史数据。 最后，请确保在实际环境中使用前对代码进行充分的测试和调整，以确保其满足你的需求并符合你的数据库架构和权限设置。