【笔记】Oracle触发器实现当一个表的某个字段被更新为空，则报错提示

背景介绍

工作中又遇到这样的问题：一张表pm_cm_payapply中的vreserve14字段，总是不知道在哪步操作中被致为空（原来的值不为空）

于是打算在表中设置一个触发器

触发器实现：当表被更新时，判断如果vreserve14被更新为空值，则提示报错

新增触发器

触发器内容：

CREATE OR REPLACE TRIGGER tri_vreserve
AFTER UPDATE OF vreserve14 ON pm_pa_payapply
DECLARE
  myexp exception
BEGIN
  IF old.vreserve14 != '' AND new.vreserve14 == '' THEN
    RAISE myexp;
  END IF;
  EXCEPTION_INIT
    WHEN myexp THEN raise_application_error('-20002','vreserve14字段的值被设置为空')；
END;

测试结果：

注意，这个错误是在提示“触发器无效且未通过重新验证”，并不是触发器成功执行的效果，我承认当时写完之后没有验证，误以为触发器已经生效，多谢 @wu_kanghua 用户的提醒。

正确的触发器内容：

-- 表换成了persons,变更的字段换成了first_name，效果都是一样的
CREATE OR REPLACE TRIGGER persons_first_name
BEFORE UPDATE OF first_name ON persons
FOR EACH ROW
DECLARE
  myexp EXCEPTION;
BEGIN
  IF ((:OLD.first_name <> '' OR :OLD.first_name IS NOT NULL) AND (:NEW.first_name IS NULL OR :NEW.first_name = '')) THEN
    RAISE myexp;
  END IF;
EXCEPTION
  WHEN myexp THEN 
    raise_application_error(-20001, 'The first_name field is updated to be empty!');
END;
/

测试：

更新前数据

select * FROM persons

更新语句

UPDATE persons SET first_name = '' WHERE person_id = 1  ;

结果：

oracle查询触发器信息

首先我们要知道这个触发器是指向哪个表，比如我们本次是在表 pm_cm_payapply 中写的触发器，那我在 all_trigger 表中通过表明table_name查询触发器名trigger_name

select trigger_name from all_triggers where table_name='PERSONS'; 

注意，表名要大写

得到触发器名为 tri_vreserve ，再通过触发器名在表all_source 表中查询触发器的具体内容

select text from all_source where type='TRIGGER' AND name='PERSONS_FIRST_NAME';

注意，触发器表名也要大写

引申：

https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm#i1863

EXCEPTION_INIT是一个将自定义异常与特定Oracle错误码关联起来的pragma。它允许您创建用户定义的异常并将其链接到特定的错误代码，从而使您能够以更有意义和可控的方式处理该错误。当您引发用户定义的异常时，您与之关联的Oracle错误代码将被传播，从而更容易在PL/SQL代码中识别和处理特定的异常。

DECLARE
  myexp EXCEPTION;
  PRAGMA EXCEPTION_INIT(myexp, -20001); -- Custom error code (negative integer)
BEGIN
  -- Some code that might raise myexp
EXCEPTION
  WHEN myexp THEN
    -- Handle the custom exception myexp
    dbms_output.put_line('Custom exception raised with error code: ' || SQLCODE);
    dbms_output.put_line('Custom exception message: ' || SQLERRM);
END;