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 ...
,nr_emp_mgr ...
,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,nr_emp_mgr)
values ('U', :new.rowid, :old.nr_emp, ... ,:old.nr_dept,:old.nr_emp_mgr);
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,nr_emp_mgr)
values ('U', null, :old.nr_emp, ... ,:old.nr_dept,:old.nr_emp_mgr);
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;