Oracle 如何在同一张表格中限制员工对经理的工资

askTom 2018-09-04

268

问题描述

我只想创建用于检查员工工资不能超过经理工资的触发器。
有一张表:

EMP(EMPNO，ENAME，JOB，MGR，HIREDATE，SALARY，DEPTNO)

FK: 经理参考EMPNO

谢谢。

专家解答

我会用不同的方式来做，因为用触发器比你想象的要困难得多

SQL> create table emp as select * from scott.emp;

Table created.

SQL> alter table emp add primary key ( empno );

Table altered.

--
-- get our data initially valid
--

SQL>
SQL> update emp set sal = 2900
  2  where empno in ( 7902,7788);

2 rows updated.

--
-- I'm going to create a materialized view that holds all of the *violations*
-- so it will be empty most of the time
--

SQL>
SQL> create materialized view log on emp with rowid including new values;

Materialized view log created.

SQL>
SQL> create materialized view emp_mv
  2  refresh fast on commit
  3  as select e1.rowid rid1, e2.rowid rid2
  4    from   emp e1,
  5           emp e2
  6    where  e1.mgr = e2.empno
  7    and    e1.sal > e2.sal;

Materialized view created.

--
-- now I add a constraint which says if I ever get a row in here, then my table data
-- must not be valid
--

SQL>   alter table emp_mv add constraint emp_mv check ( 1=0);

Table altered.

SQL> update emp set sal = 3100
  2  where empno = 7902;

1 row updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (MCDONAC.EMP_MV) violated

复制