案例:scott.emp表的销售员工资只能增加,不能减少
代码:
CREATE OR REPLACE TRIGGER tr_emp_upd
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
--
当更新销售员时才触发
WHEN (OLD.job='SALESMAN')
DECLARE
BEGIN
IF(:NEW.sal<:OLD.sal) THEN
raise_application_error(-20013,'销售员工资不能降低!');
END IF;
END;
WHEN (OLD.job='SALESMAN')
DECLARE
BEGIN
IF(:NEW.sal<:OLD.sal) THEN
raise_application_error(-20013,'销售员工资不能降低!');
END IF;
END;
运行情况:
SQL> update emp set sal=sal-1000 where job='MANAGER';
3 rows updated
SQL> update emp set sal=sal-1000 where job='SALESMAN';
ORA-20013: 销售员工资不能降低!
ORA-06512: 在"SCOTT.TR_EMP_UPD", line 4
ORA-04088: 触发器 'SCOTT.TR_EMP_UPD' 执行过程中出错