[原创]ORA-04098: 触发器无效且未通过重新验证问题

今天用写了一个 trigger, 在 dbvis 中运行没有错误, 编译也没错, 但运行时报错如下:

09:52:52 [INSERT - 0 row(s), 0.047 secs] [Error Code: 4098, SQL State: 42000] ORA-04098: 触发器 'OAS.MIGUSER_BASEINFO_USERID' 无效且未通过重新验证

很是奇怪. 我把 trigger 的执行内容全部删除也会报错.

创建 trigger 语句如下:

create or replace trigger OAS.MIGUSER_BASEINFO_USERID
BEFORE INSERT or update ON OAS.MIGUSER_BASEINFO
REFERENCING NEW AS newRow OLD AS oldRow
FOR EACH ROW
BEGIN
    if updating then
        if :oldRow.BUSINESS_STATUS = 'N' and :newRow.BUSINESS_STATUS != 'N' then
              select sysdate into :newRow.activeDate from dual
        end if
    elseif inserting then
       SELECT SQ_AUTOINC_MIGUSER_BASEINFO.NEXTVAL INTO :newRow.USERID FROM dual
       if :newRow.BUSINESS_STATUS != 'N' then
             select sysdate into :newRow.activeDate from dual
       end if
    end if
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise

       RAISE
END MIGUSER_BASEINFO_USERID

 

编译 trigger 语句如下:

ALTER TRIGGER OAS.MIGUSER_BASEINFO_USERID COMPILE;
commit;

后来把问题转给了我们的 DBA, 他把每个单独语句后面加上了分号, 然后就成了. 但我在 DBVIS 中运行如果带分号就会报错. 很是奇怪. 我想大概是 dbvis 的问题.