Oracle数据库中无法用触发器审计表问题求助

目标 #

创建名为audit_users的触发器，对Users表的增删改操作进行审计，将操作类型及新旧数据存入users_audit表。

表结构DDL #

create table Users (
    username varchar2(30) not null constraint users_pk primary key,
    first_name varchar2(30) not null,
    last_name varchar2(30),
    age number not null
);

CREATE TABLE users_audit (
    new_first_name varchar2(30),
    old_first_name varchar2(30),
    new_last_name varchar2(30),
    old_last_name varchar2(30),
    new_age NUMBER,
    old_age NUMBER,
    action varchar2(30)
);

已创建的触发器 #

CREATE OR REPLACE TRIGGER audit_users
BEFORE INSERT OR DELETE OR UPDATE ON Users
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO users_audit VALUES(
            :NEW.first_name, NULL, 
            :NEW.last_name, NULL,
            :NEW.age, NULL,
            'insert'
        );
    ELSIF UPDATING THEN
        INSERT INTO users_audit VALUES(
            :NEW.first_name, :OLD.first_name, 
            :NEW.last_name, :OLD.last_name,
            :NEW.age, :OLD.age,
            'update'
        );
    ELSIF DELETING THEN
        INSERT INTO users_audit VALUES(
            NULL, :OLD.first_name, 
            NULL, :OLD.last_name,
            NULL, :OLD.age,
            'delete'
        );
    END IF;
END;
/

错误情况 #

执行以下DML语句时均触发错误：

INSERT INTO Users VALUES ('jackie', 'Jackie', 'Chan', 60);
UPDATE Users SET age = 61 WHERE username = 'jackie';
DELETE FROM Users WHERE username = 'jackie';

错误信息：

ORA-04098: 触发器 'SQL_HMENLXODFUUNAEXBEDJCPFQUV.USERS_AUDIT' 无效，重新验证失败

环境 #

Oracle Live SQL（入门学习环境）

问题分析与解决步骤 #

问题根源 #

错误提示的触发器名称为USERS_AUDIT，但你创建的触发器是audit_users，说明存在一个与Users表关联的无效触发器USERS_AUDIT，执行DML时数据库尝试触发所有相关触发器，包括这个无效的，导致报错。

解决步骤 #

1. 查询关联触发器状态
   执行以下SQL查看Users表的所有触发器及其状态：

SELECT trigger_name, status FROM user_triggers WHERE table_name = 'USERS';

2. 删除无效触发器
   如果查询结果中存在USERS_AUDIT且状态为INVALID，执行删除语句：

DROP TRIGGER USERS_AUDIT;

3. 编译现有触发器
   确保audit_users触发器状态有效，执行编译语句：

ALTER TRIGGER audit_users COMPILE;

4. 优化触发器（可选）
   为避免列顺序变化导致的潜在问题，建议在插入users_audit时显式指定列名，修改后的触发器代码如下：

CREATE OR REPLACE TRIGGER audit_users
BEFORE INSERT OR DELETE OR UPDATE ON Users
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO users_audit (
            new_first_name, old_first_name, 
            new_last_name, old_last_name,
            new_age, old_age, action
        ) VALUES(
            :NEW.first_name, NULL, 
            :NEW.last_name, NULL,
            :NEW.age, NULL, 'insert'
        );
    ELSIF UPDATING THEN
        INSERT INTO users_audit (
            new_first_name, old_first_name, 
            new_last_name, old_last_name,
            new_age, old_age, action
        ) VALUES(
            :NEW.first_name, :OLD.first_name, 
            :NEW.last_name, :OLD.last_name,
            :NEW.age, :OLD.age, 'update'
        );
    ELSIF DELETING THEN
        INSERT INTO users_audit (
            new_first_name, old_first_name, 
            new_last_name, old_last_name,
            new_age, old_age, action
        ) VALUES(
            NULL, :OLD.first_name, 
            NULL, :OLD.last_name,
            NULL, :OLD.age, 'delete'
        );
    END IF;
END;
/

5. 重新测试DML操作
   再次执行之前的增删改语句，验证审计记录是否正常写入users_audit表。

内容的提问来源于stack exchange，提问作者Akanksha Sharma