SQL> create or replace trigger emp_trigger
2 before update of SAL on emp
3 for each row
4 declare
5 -- this pragma will allow our trigger to commit and
6 -- capture an audit trail record. We can then fail
7 -- the triggering statement, preventing the update from
8 -- occurring
9 pragma autonomous_transaction;
10 l_cnt number;
11 begin
12 -- this query just makes sure the empno being updated
13 -- is a person who reports to the employee doing the update.
14 -- connect by is a nice way to build this hierarchy.
15 -- since the where clause is processed after the hierarchy
16 -- is built, we can easily use exists on this
17 select count(*)
18 into l_cnt
19 from dual
20 where exists ( select empno
21 from emp
22 where empno = :new.empno
23 start with mgr =
24 (select empno from emp where ename=USER)
25 connect by prior empno = mgr );
26
27 -- If the exists returns no rows, we have attempted
28 -- to update someone who does not work for us. We
29 -- will audit the attempt as well as fail the attempt.
30 -- the employee salary will not be updated and we will
31 -- have a record of the attempt
32 if ( l_cnt = 0 )
33 then
34 insert into audit_tab values ( user, sysdate,
35 'Attempt to update salary of ' ||
36 :new.ename || '-' || :new.empno);
37 commit;
38
39 raise_application_error( -20001,
40 'You have tried to do something you should '||
41 'not have and we know it');
42 end if;
43 end;
44 /
Trigger created.