触发器问题,提示:ORA-04098:触发器无效或未通过重新验证

Question

create table system.test_bin( id number(3),name varchar2(3));
create sequence system.test_sql increment by 1 start with 1 maxvalue 1000;
Create or Replace Trigger system.test_tig Before Insert Or Update of id on system.test_bin
FOR EACH ROW
Begin
if inserting then
select to_number(test_sql.nextval) into :new.id from dual;

else
raise_application_error(-20002,'更新id失败');
end if;
end;

最后往里查数据:insert into system.test_bin(id) values(3);
提示:ORA-04098:触发器无效或未通过重新验证
请回答我的问题

Answer 1

SQL> --3、创建触发器t_emp1
SQL> create or replace trigger t_emp1
2 before update of sal on emp1
3 referencing old as o new as n
4 for each row
5 BEGIN
6 insert into old_emp1 values(: o.empno, : o.sal, : n.sal);
7 end t_emp1;
8 /

警告: 创建的触发器带有编译错误。

SQL>
SQL> show err
TRIGGER T_EMP1 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00801: 内部错误 [ph2csql_strdef_to_diana:bind]
2/5 PL/SQL: SQL Statement ignored
2/17 PL/SQL: ORA-06544: PL/SQL: 内部错误, 参数:
[ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

SQL> create or replace trigger t_emp1
2 before update of sal on emp1
3 referencing old as o new as n
4 for each row
5 BEGIN
6 insert into old_emp1 values(:o.empno, :o.sal, :n.sal);
7 end t_emp1;
8 /

触发器已创建

: 与 o 之间， 不要有空格。

: o.empno, : o.sal, : n.sal;
修改为：
:o.empno, :o.sal, :n.sal