Oracle使用触发器限制周日建表触发ORA-30506错误咨询

报错原因 #

ORA-30506报错是原代码存在多处语法和逻辑错误导致的，具体问题如下：

• DDL类系统触发器不支持FOR EACH ROW行级触发语法，该语法仅适用于表级DML触发器，DDL触发器为语句级触发，需要删除该行
• 触发对象声明错误，BEFORE CREATE ON ALSPRD的写法会让Oracle误认为你要将触发器绑定到名为ALSPRD的表/视图上，系统触发器绑定schema需要加.SCHEMA后缀，绑定全库则声明ON DATABASE
• 触发器内部不能直接执行REVOKE这类DDL语句，且逻辑设计不合理：临时回收权限的方式需要额外逻辑在周一恢复权限，漏操作会导致用户永久失去建表权限
• 日期判断未指定语言参数，非英文环境下TO_CHAR(sysdate,'DY')返回的周日缩写不是SUN，会导致判断失效

正确实现代码 #

CREATE OR REPLACE TRIGGER sunday_trigger
-- 限制ALSPRD schema下的CREATE操作，如需限制全库所有用户改为 ON DATABASE
BEFORE CREATE ON ALSPRD.SCHEMA
DECLARE
    v_day VARCHAR2(3);
BEGIN
    -- 指定日期语言避免多环境适配问题
    v_day := TO_CHAR(SYSDATE, 'DY', 'nls_date_language=american');
    IF v_day = 'SUN' THEN
        -- 抛出自定义异常直接终止CREATE操作
        RAISE_APPLICATION_ERROR(-20001, '周日禁止创建表，请切换至工作日操作');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

注意事项 #

• 创建该触发器需要持有ADMINISTER DATABASE TRIGGER系统权限，建议使用SYS或SYSTEM用户操作
• 如果需要限制所有类型的DDL操作，可将触发时机修改为BEFORE DDL ON xxx

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