BEFORE触发器RAISE前代码未执行原因及Schema防删截方法咨询

嘿，这个问题我之前帮同事排查过好几次，咱们拆成两部分来解答：

一、为什么抛出错误时logTable没有记录？ #

这是Oracle BEFORE触发器的事务特性导致的：

• 当你在BEFORE触发器里执行INSERT到logTable，然后抛出RAISE_APPLICATION_ERROR时，整个触发事件（DROP/TRUNCATE）和触发器内的所有操作会被整体回滚。因为触发器和主操作属于同一个事务，一旦触发器抛出错误，Oracle会撤销该事务内的所有变更，包括你刚插入的日志记录。
• 当你删掉RAISE_APPLICATION_ERROR语句后，触发器里的INSERT正常执行并提交（因为主操作完成后事务会提交），但由于没有错误阻止，DROP/TRUNCATE操作自然会继续执行。

二、如何阻止指定Schema下的DROP/TRUNCATE操作？ #

你提到INSTEAD OF触发器只对Schema所有者生效，那咱们用数据库级BEFORE触发器+自治事务的方案，既能阻止操作，又能保留日志：

核心思路： #

1. 用**自治事务（AUTONOMOUS_TRANSACTION）**分离日志插入和主操作事务，确保即使主操作被回滚，日志依然能保存。
2. 在触发器中通过系统变量ORA_DICT_OBJ_SCHEMA判断操作的对象所属Schema，匹配目标Schema时抛出错误阻止操作。

示例代码： #

-- 先确保logTable存在（示例结构，你可以根据实际调整）
CREATE TABLE logTable (
    operation VARCHAR2(50),
    obj_name VARCHAR2(128),
    obj_schema VARCHAR2(128),
    op_time TIMESTAMP,
    username VARCHAR2(128)
);

-- 创建触发器
CREATE OR REPLACE TRIGGER block_schema_drop_trigger
BEFORE DROP OR TRUNCATE ON DATABASE
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION; -- 开启自治事务，让日志操作独立于主事务
BEGIN
    -- 仅拦截目标Schema下的表操作（可根据需求调整，比如去掉表判断拦截所有对象）
    IF ORA_DICT_OBJ_SCHEMA = 'YOUR_TARGET_SCHEMA' AND ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        -- 插入操作日志
        INSERT INTO logTable (operation, obj_name, obj_schema, op_time, username)
        VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_SCHEMA, SYSTIMESTAMP, USER);
        
        COMMIT; -- 提交自治事务，确保日志被持久化
        
        -- 抛出错误阻止DROP/TRUNCATE操作
        RAISE_APPLICATION_ERROR(-20001, '禁止对Schema ' || ORA_DICT_OBJ_SCHEMA || ' 执行' || ORA_SYSEVENT || '操作');
    END IF;
END;
/

注意事项： #

• 触发器创建者需要有CREATE TRIGGER ON DATABASE的权限（通常需要DBA角色）。
• 自治事务要谨慎使用，避免不必要的事务拆分导致数据不一致。
• 可以根据需求扩展判断条件，比如只拦截特定表、特定用户的操作等。

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