【博客文章2026】MySQL 9.x数据库管理技巧11：触发器、基于值的审计和事件---与Oracle数据库作对比
Author: Bo Tang 1. MySQL数据库的触发器与Oracle数据库的触发器作对比：
1.1 在MySQL 9.6数据库中创建DML型的触发器，在world_y数据库中实现基于值的审计： 1.1.1 首先创建一个用于存放旧值的表：
[root@station95 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 9.6.0 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use world_y; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> create table audit_country(Code char(3),name char(52), capital int, -> Code2 char(2), action varchar(10), action_time timestamp); Query OK, 0 rows affected (0.021 sec) |
1.1.2 创建update触发器，实现基于值的审计（记录变更前的值）：
mysql> DELIMITER // mysql> CREATE TRIGGER audit_country_update -> AFTER UPDATE ON country -> FOR EACH ROW -> BEGIN -> IF OLD.code <> NEW.code -> OR OLD.name <> NEW.name -> OR OLD.capital <> NEW.capital -> OR OLD.code2 <> NEW.code2 THEN -> INSERT INTO audit_country (code, name, capital, code2, action, action_time) -> VALUES (OLD.code, OLD.name, OLD.capital, OLD.code2, 'UPDATE', NOW()); -> END IF; -> END// Query OK, 0 rows affected (0.019 sec)
mysql> DELIMITER ; |
使用“show triggers”命令来查看MySQL数据库里的触发器的状态信息：
mysql> show triggers\G *************************** 1. row *************************** Trigger: audit_country_update Event: UPDATE Table: country Statement: BEGIN IF OLD.code <> NEW.code OR OLD.name <> NEW.name OR OLD.capital <> NEW.capital OR OLD.code2 <> NEW.code2 THEN INSERT INTO audit_country (code, name, capital, code2, action, action_time) VALUES (OLD.code, OLD.name, OLD.capital, OLD.code2, 'UPDATE', NOW()); END IF; END Timing: AFTER Created: 2026-04-10 17:23:24.83 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.003 sec) |
测试更新操作：
mysql> update country set Capital=9999 where Code='ZWE'; Query OK, 1 row affected (0.005 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> show status like 'auto_commit'; Empty set (0.049 sec)
mysql> commit; Query OK, 0 rows affected (0.000 sec)
mysql> select * from country where Code='ZWE'; +------+----------+---------+-------+ | Code | Name | Capital | Code2 | +------+----------+---------+-------+ | ZWE | Zimbabwe | 9999 | ZW | +------+----------+---------+-------+ 1 row in set (0.001 sec) |
查看基于值的审计的结果：
mysql> select * from audit_country; +------+----------+---------+-------+--------+---------------------+ | Code | name | capital | Code2 | action | action_time | +------+----------+---------+-------+--------+---------------------+ | ZWE | Zimbabwe | 4068 | ZW | UPDATE | 2026-04-10 18:29:51 | +------+----------+---------+-------+--------+---------------------+ 1 row in set (0.001 sec) |
1.1.2 创建delete触发器，实现基于值的审计（记录删除前的值）：
mysql> DELIMITER // mysql> CREATE TRIGGER audit_country_delete -> AFTER DELETE ON country -> FOR EACH ROW -> BEGIN -> INSERT INTO audit_country (code, name, capital, code2, action, action_time) -> VALUES (OLD.code, OLD.name, OLD.capital, OLD.code2, 'DELETE', NOW()); -> END// Query OK, 0 rows affected (0.007 sec)
mysql> DELIMITER ; |
使用“show triggers”命令来查看MySQL数据库里的触发器的状态信息：
mysql> show triggers\G ...... *************************** 2. row *************************** Trigger: audit_country_delete Event: DELETE Table: country Statement: BEGIN INSERT INTO audit_country (code, name, capital, code2, action, action_time) VALUES (OLD.code, OLD.name, OLD.capital, OLD.code2, 'DELETE', NOW()); END Timing: AFTER Created: 2026-04-10 22:49:53.59 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.003 sec) |
测试删除操作：
mysql> delete from countrylanguage where CountryCode='ZWE'; Query OK, 4 rows affected (0.002 sec)
mysql> delete from country where Code='ZWE'; Query OK, 1 row affected (0.002 sec)
mysql> select * from country where Code='ZWE'; Empty set (0.001 sec) |
查看基于值的审计的结果：
mysql> select * from audit_country; +------+----------+---------+-------+--------+---------------------+ | Code | name | capital | Code2 | action | action_time | +------+----------+---------+-------+--------+---------------------+ | ZWE | Zimbabwe | 4068 | ZW | UPDATE | 2026-04-10 18:29:51 | | ZWE | Zimbabwe | 9999 | ZW | DELETE | 2026-04-10 23:06:08 | +------+----------+---------+-------+--------+---------------------+ 2 rows in set (0.001 sec) |
1.2 在Oracle数据库（19.3.0）中DML型的触发器，实现基于值的审计： 由于在上一篇博客中已将数据从MySQL数据库导出并导入Oracle数据库，所以我们可以在Oracle数据库作如下对比实验，创建功能相同的DML型的触发器，实现基于值的审计： [oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 10 23:22:18 2026 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn hr/oracle_4U Connected. SQL> create table audit_country(Code char(3),name char(52), capital int, 2 Code2 char(2), action varchar(10), action_time timestamp);
Table created. |
1.2.2 创建触发器，实现基于值的审计（记录变更前的值和删除前的值）： Oracle可以把update和delete同时进行审计：
SQL> CREATE OR REPLACE TRIGGER audit_country_trg 2 AFTER UPDATE OR DELETE ON country 3 REFERENCING NEW AS NEW OLD AS OLD 4 FOR EACH ROW 5 BEGIN 6 IF UPDATING AND (:OLD.code <> :NEW.code 7 OR :OLD.name <> :NEW.name 8 OR :OLD.capital <> :NEW.capital 9 OR :OLD.code2 <> :NEW.code2) 10 THEN 11 INSERT INTO audit_country (code, name, capital, code2, action, action_time) 12 VALUES (:OLD.code, :OLD.name, :OLD.capital, :OLD.code2, 'UPDATE', systimestamp); 13 ELSIF DELETING THEN 14 INSERT INTO audit_country (code, name, capital, code2, action, action_time) 15 VALUES (:OLD.code, :OLD.name, :OLD.capital, :OLD.code2, 'DELETE', systimestamp); 16 END IF; 17 END; 18 /
Trigger created. |
使用dba_trigger来查看Oracle数据库里的触发器的状态信息：
SQL> set linesize 1000 SQL> col TRIGGER_NAME format a15 trunc SQL> col TRIGGERING_EVENT format a20 trunc SQL> col WHEN_CLAUSE format a20 trunc SQL> col TABLE_OWNER format a10 trunc SQL> col COLUMN_NAME format a10 trunc SQL> select TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,COLUMN_NAME, status, ACTION_TYPE from user_triggers where trigger_name='AUDIT_COUNTRY_TRG';
TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNE COLUMN_NAM STATUS ACTION_TYPE --------------- ---------------- -------------------- ---------- ---------- -------- ----------- AUDIT_COUNTRY_T AFTER EACH ROW UPDATE OR DELETE HR ENABLED PL/SQL |
测试更新操作：
SQL> update country set Capital=9999 where Code='ZWE';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from country where Code='ZWE';
COD NAME CAPITAL CO --- ---------------------------------------------------- ---------- -- ZWE Zimbabwe 9999 ZW |
查看基于值的审计的结果：
SQL> select * from audit_country;
COD NAME CAPITAL CO ACTION ACTION_TIME --- ---------------------------------------------------- ---------- -- ---------- --------------------------------------------------------------------------- ZWE Zimbabwe 4068 ZW UPDATE 11-APR-26 05.33.49.772917 A |
测试删除操作：
SQL> delete from countrylanguage where CountryCode='ZWE';
4 rows deleted.
SQL> delete from country where Code='ZWE';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from country where Code='ZWE';
no rows selected |
查看基于值的审计的结果：
SQL> select * from audit_country;
COD NAME CAPITAL CO ACTION ACTION_TIME --- ---------------------------------------------------- ---------- -- ---------- --------------------------------------------------------------------------- ZWE Zimbabwe 4068 ZW UPDATE 11-APR-26 05.33.49.772917 AM ZWE Zimbabwe 9999 ZW DELETE 11-APR-26 05.41.25.311883 AM |
2. MySQL数据库的事件与Oracle数据库的Scheduler作对比：
MySQL EVENT在MySQL事件调度器（Event Scheduler）线程中运行，语法简单，可内嵌SQL语句或调用存储过程。 2.1 在MySQL 9.6数据库中创建事件，用于控制test用户的需要长时间运行的SQL语句：
mysql> DELIMITER // mysql> CREATE EVENT ctrl_long_session ON SCHEDULE EVERY 15 SECOND -> DO -> BEGIN -> DECLARE var_pid INT; -> DECLARE cur_process CURSOR FOR SELECT id -> FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME>30 AND USER = 'test'; -> OPEN cur_process; -> BEGIN -> DECLARE EXIT HANDLER FOR NOT FOUND -> BEGIN -> CLOSE cur_process; -> END; -> LOOP -> FETCH cur_process INTO var_pid; -> kill var_pid; -> END LOOP; -> END; -> END//
Query OK, 0 rows affected (0.013 sec) |
使用“show create event”命令来查看MySQL数据库里的事件的DDL信息：
mysql> show create event ctrl_long_session\G *************************** 1. row *************************** Event: ctrl_long_session sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`localhost` EVENT `ctrl_long_session` ON SCHEDULE EVERY 15 SECOND STARTS '2026-04-11 19:22:16' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN DECLARE var_pid INT; DECLARE cur_process CURSOR FOR SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME>30 AND USER = 'test'; OPEN cur_process; BEGIN DECLARE EXIT HANDLER FOR NOT FOUND BEGIN CLOSE cur_process; END; LOOP FETCH cur_process INTO var_pid; kill var_pid; END LOOP; END; END character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.001 sec) |
测该试事件，用来控制test用户的长时间运行的SQL语句： 打开MySQL事件功能：
mysql> set global event_scheduler=on; Query OK, 0 rows affected (0.001 sec) |
创建测试用户：
mysql> create user 'test'@'localhost'identified by 'oracle_4U'; Query OK, 0 rows affected (0.019 sec)
mysql> grant select on world_y.* to 'test'@'localhost'; Query OK, 0 rows affected (0.002 sec) |
执行需要长时间运行的SQL语句： 由于运行了超过30秒的SQL语句，再加上观察窗口的15秒，所以如果执行等于或超过46秒的SQL语句，必然会被事件ctrl_long_session控制到：
[root@station95 ~]# mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1627 Server version: 9.6.0 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use world_y; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> select sleep(46); ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... Connection id: 1633 Current database: world_y
ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... Connection id: 1637 Current database: world_y
ERROR 2013 (HY000): Lost connection to MySQL server during query |
2.2 在Oracle数据库中创建Scheduler，用于控制hr用户的需要长时间运行的SQL语句： 在Oracle 数据库中，MySQL的CREATE EVENT没有完全一一对应的功能，但最接近的替代或类比对象是DBMS_SCHEDULER。DBMS_SCHEDULER创建Schedule job，用于在数据库内部按预定时间执行存储过程或PL/SQL代码块。与MySQL数据库的create event相比，Oracle DBMS_SCHEDULER功能强大得多，是Oracle企业调度框架的核心。虽然语法和功能比MySQL复杂，但基本概念一致：按计划执行任务。 我们可以在Oracle数据库作如下对比实验：创建功能相同的Scheduler Job，用于控制hr用户的需要长时间运行的SQL语句： 首先以sys用户创建存储过程，用于kill session：
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 12 05:33:08 2026 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba Connected. SQL> CREATE OR REPLACE PROCEDURE kill_long_sessions 2 is 3 CURSOR cur_process IS 4 SELECT sid, serial# 5 FROM v$session 6 WHERE last_call_et > 30 7 AND username = 'HR' 8 AND status = 'ACTIVE' 9 AND type != 'BACKGROUND'; 10 BEGIN 11 FOR rec IN cur_process 12 LOOP 13 BEGIN 14 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||rec.sid || ',' || rec.serial# ||''' IMMEDIATE'; 15 EXCEPTION 16 WHEN OTHERS THEN 17 exit; 18 END; 19 END LOOP; 20 IF cur_process%NOTFOUND THEN 21 DBMS_OUTPUT.put_line('No long running sessions found for user HR'); 22 END IF; 23 EXCEPTION 24 WHEN OTHERS THEN 25 DBMS_OUTPUT.put_line('Error in kill_long_sessions: ' || SQLERRM); 26 RAISE; 27 END kill_long_sessions; 28 /
Procedure created. |
接着，以sys用户使用dbms_scheduler创建作业。作业内部调用上面的kill_long_session存储过程：
SQL> BEGIN 2 DBMS_SCHEDULER.CREATE_JOB ( 3 job_name => 'CTRL_LONG_SESSION_JOB', 4 job_type => 'PLSQL_BLOCK', 5 job_action => 'BEGIN kill_long_sessions; END;', 6 start_date => SYSTIMESTAMP, 7 repeat_interval => 'FREQ=SECONDLY; INTERVAL=15', 8 enabled => TRUE, 9 comments => 'Kill test user sessions running more than 30 seconds' 10 ); 11 END; 12 /
PL/SQL procedure successfully completed. |
查看Oracle数据库中的scheduler信息：
SQL> set linesize 1000 SQL> col job_name format a25 trunc SQL> SELECT job_name, enabled, state,to_char(last_start_date,'YYYY-MM-DD:HH24:MI:SS'),to_char(next_run_date,'YYYY-MM-DD:HH24:MI:SS') 2 from dba_scheduler_jobs WHERE job_name = 'CTRL_LONG_SESSION_JOB';
JOB_NAME ENABL STATE TO_CHAR(LAST_START_ TO_CHAR(NEXT_RUN_DA ------------------------- ----- --------------- ------------------- ------------------- CTRL_LONG_SESSION_JOB TRUE SCHEDULED 2026-04-12:06:11:01 2026-04-12:06:11:16 |
测试该Scheduler Job，用来控制HR用户的长时间运行的SQL语句： 为了测试，授权HR用户执行dbms_lock（对应MySQL数据库中的sleep函数）：
SQL> grant execute on dbms_lock to hr;
Grant succeeded. |
执行需要长时间运行的SQL语句： 由于运行了超过30秒的SQL语句，再加上观察窗口的15秒，所以如果执行了等于或超过46秒的SQL语句，必然会被Scheduler job ctrl_long_session_job控制到：
SQL> conn hr/oracle_4U Connected. SQL> exec DBMS_LOCK.SLEEP(46); BEGIN DBMS_LOCK.SLEEP(46); END;
* ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 929728 Session ID: 90 Serial number: 640 |
|
