如何使用触发器调用存储过程实现Department表DML操作限制

表结构与初始化数据 #

create table department(deptno number, deptname varchar2(50), deptloc varchar2(50));

insert into department values(1,'A','X');
insert into department values(2,'B','Y');
insert into department values(3,'C','Z');

原始存储过程（存在问题） #

create or replace procedure secure_dml(i_month IN varchar2)
is

begin
if i_month <> 'March' then
dbms_output.put_line('You can modify or add a department only at the end of a financial year');
else 
--should I write insert/update DML statement?

end;

原始触发器（存在问题） #

create or replace trigger tr_check_dept before insert on department
begin
dbms_output.put_line('Record inserted');
end;

• 仅允许在3月对Department表执行数据修改操作
• 创建名为SECURE_DML的存储过程：除3月外其余时间阻止DML语句执行，若用户在非3月尝试修改表，存储过程需输出提示：You can modify or add a department only at the end of a financial year
• 在Department表上创建名为TR_CHECK_DEPT的语句级触发器，触发时调用上述存储过程
• 通过向Department表插入新记录测试功能有效性

修正后存储过程 #

说明：移除了手动传月份的入参，改为自动读取系统当前月份，非3月时主动抛出异常终止DML执行

CREATE OR REPLACE PROCEDURE secure_dml
IS
    v_current_month VARCHAR2(20);
BEGIN
    -- 读取当前系统月份，英文全称格式，避免多语言环境问题
    SELECT TO_CHAR(SYSDATE, 'FMMonth', 'nls_date_language = ENGLISH') 
    INTO v_current_month FROM DUAL;
    
    IF v_current_month != 'March' THEN
        DBMS_OUTPUT.PUT_LINE('You can modify or add a department only at the end of a financial year');
        -- 抛出自定义异常，阻止后续DML操作执行
        RAISE_APPLICATION_ERROR(-20001, 'Department表仅允许在每年3月执行数据修改操作');
    END IF;
END;
/

修正后触发器 #

说明：调整触发时机为所有DML操作前触发，触发逻辑改为调用校验存储过程

CREATE OR REPLACE TRIGGER tr_check_dept
BEFORE INSERT OR UPDATE OR DELETE ON department
-- 语句级触发器，无需逐行校验
BEGIN
    secure_dml;
END;
/

1. 非3月场景测试
   执行插入SQL：

INSERT INTO department VALUES(4, 'D', 'W');

预期结果：控制台输出指定提示信息，同时抛出-20001错误，插入操作失败。
2. 3月场景测试
将系统时间调整为3月后执行相同插入SQL，操作执行成功，执行SELECT * FROM department;可查询到新增的第四条数据。

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