You Asked
I just want to create triggers for checking salary that employee salary cannot exceed manager's.
there are one table:
EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SALARY,DEPTNO)
FK:MGR references EMPNO
Thanks.
and Connor said...
I'll do it a different way because it is much harder than you think to do it with triggers
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