大家帮我看下触发器报这个错:ora 04098 触发器无效未通过验证
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
表结构是这样的,为什么触发器会报错啊,找不到哪里错了,大家帮忙看下哦
create trigger emp_trigger2
before update of sal,comm or delete
on emp
for each row
when (old.job='SALESMAN')
BEGIN
case when updating('sal') then
if :new.sal< :old.sal then
raise_application_error(-20001,'职业为saleman的员工工资不能降');
end if;
when updating('comm') then
if :new.comm< :old.comm then
raise_application_error((-20001,'职业为saleman的员工comm不能降');
end if;
when deleting
raise_application_error(-20003,'职业为saleman的员工不能删');
end case;
end
drop trigger emp_trigger
update emp set sal=2000.00 where ename='ALLEN' and job='SALEMAN'
------解决方案--------------------
SQL code
create or replace trigger emp_trigger2
before update of sal,comm or delete
on emp
for each row
when (old.job='SALESMAN')
BEGIN
case
when updating('sal') then
if :new.sal< :old.sal then
raise_application_error(-20001,'职业为saleman的员工工资不能降');
end if;
when updating('comm') then
if :new.comm< :old.comm then
raise_application_error(-20001,'职业为saleman的员工comm不能降');--多个括号
end if;
when deleting then --少个then
raise_application_error(-20003,'职业为saleman的员工不能删');
end case;
end;
/
--你的测试部分估计也不会有你想要的结果,job='SALESMAN'吧,还有ename注意大小写,可用upper(ename)=...
update emp set sal=2000.00 where ename='ALLEN' and job='SALEMAN'