How do you prefer your constraints ?

create global temporary table EMP_TE -- with this trigger, heading of table EMP is defined and must be maintained in two distinct places (DML char(1) not null check (DML in ('I' ,'U', 'D')) ,ROW_ID rowid ,nr_emp ... ,nm_job ... ,dt_hired ... ,cd_sgrade ... ,am_sal ... ,nr_dept ... ,check(DML<>'I' or row_id is not null) ,check(DML<>'U' or row_id is not null) ,check(DML<>'D' or row_id is null) ) on commit delete rows ; create trigger EMP_BIUD_TE before insert or update or delete on EMP begin -- reset before every DML delete from EMP_TE; end; create trigger EMP_AIUDR_TE after insert or delete or update on EMP for each row begin -- register statement tuple if INSERTING then -- only store 'pointer' to inserted row insert into EMP_TE (DML,ROW_ID) values ('I', :new.rowid); elseif UPDATING then -- snapshot of old row plus pointer to replacing row insert into EMP_TE (DML,row_id,nr_emp,nm_job,dt_hired,cd_sgrade,am_sal,nr_dept) values ('U', :new.rowid, :old.nr_emp, ... ,old.nr_dept); elseif DELETING --snapshot of old row insert into EMP_TE (DML,row_id,nr_emp,nm_job,dt_hired,cd_sgrade,am_sal,nr_dept) values ('U', null, :old.nr_emp, ... ,old.nr_dept); end if; -- end; create view v_emp_ITE as select e.* from emp_te te, emp e where DML='I' and te.row_id = e.rowid; create view v_emp_ute as select e.nr_emp as N_nr_emp, e.nm_job as n_nm_job, ... e.nr_dept as n_nr_dept, te.nr_emp as o_nr_emp, te.nm_job as o_nm_job, ... te.nr_dept as o_nr_dept from emp_te te, emp e where DML = 'U' and te.ROW_ID = e.rowid; create view v_emp_dte as select nr_emp, nm_job, ..., nr_dept from emp_te where DML='D'; create trigger EMP_AIS_R47 after insert on EMP declare pl_dummy varchar(40); begin -- Inserting an employee. Check salary < manager's salary for r in (select distinct nr_emp,am_sal,nr_emp_mgr from v_EMP_ite i) loop begin -- acquire serialization lock p_request_lock('R47'||to_char(r.nr_emp)); -- select 'Constraint R47 is satisfied' into pl_dummy from DUAL where exists ( select 'manager with higher salary' from EMP e where e.nr_emp = r.nr_emp_mgr and e.am_sal >= r.am_sal ); ) -- exception when no_data_found then -- raise_application_error (-20999, 'Constraint R47 is violated for employee'||to_char(r.nr_emp)); -- end; end loop; -- end; create trigger EMP_AUS_R47 after update on EMP declare pl_dummy varchar(80) begin -- changing a manager or salary requires checking against new manager's salary for r in (select n_nr_emp as nr_emp, n_am_sal as am_sal, n_nr_emp_mgr as nr_emp_mgr from v_emp_ute e where (e.o_nr_emp=e.n_nr_emp and (e.n_am_sal > e.o_am_sal || e.n_nr_emp_mgr <> e.o_nr_emp_mgr)) loop begin -- acquire serialization lock p_request_lock('R47'||to_char(r.nr_emp)); -- have to take into account simultaneous raises for the manager select 'Constraint R47 is satisfied' into pl_dummy from DUAL where not exists (select 'This manager has a lower salary than one of his employees' from ((select * from EMP except select * from v_emp_dte) union (select * from v_emp_ite)) ne where ne.nr_emp = r.nr_emp_mgr and ne.am_sal < r.am_sal); -- exception when no_data_found then -- raise_application_error (-20999, 'Constraint R47 is violated for employee'||to_char(r.nr_emp)); -- end; end loop; -- end; or assert databaseconstraint,databaseconstraint( tuple(constraintlabel(R47 - Non-Salesreps cannot earn more than the employee they report to.)errorcode(115147)sp_expression( restrict\(join\(emp,project\(rename\(emp,\(nr_emp_mgr,mgrmgr,nr_emp,nr_emp_mgr,am_sal_mthly,am_sal_mthly_mgr\)\),\(nr_emp_mgr,am_sal_mthly_mgr\)\)\),and\(not\(eq\(nm_job,name\(salesrep\)\)\),gt\(am_sal_mthly,am_sal_mthly_mgr\)\)\) )) |> ) assert constraintmessagetext,constraintmessagetext( tuple(errorcode(115147)constraintmessagetext(Employee §NR_EMP§ earns more than his manager §NR_EMP_MGR§.)) |> )