How to write triggers to enforce business rules?

Question

I want to create triggers for practicing PL/SQL and I sorta got stuck with these two ones, which I'm sure they're simple, but I can't get a hold of this code.

The first trigger forbids an employee to have a salary higher than the 80% of their boss (The code is incomplete because I don't know how to continue):

CREATE OR REPLACE TRIGGER MAX_SALARY
BEFORE INSERT ON EMP
FOR EACH ROW
P.BOSS EMP.JOB%TYPE := 'BOSS'
P.SALARY EMP.SAL%TYPE
BEGIN
SELECT SAL FROM EMP
WHERE  
 JOB != P.BOSS
...

And the second one, there must not be less than two employees per department

CREATE TRIGGER MIN_LIMIT
AFTER DELETE OR UPDATE EMPNO
EMPLOYEES NUMBER(2,0);
BEGIN
SELECT COUNT(EMPNO)INTO EMPLOYEES FROM EMP
WHERE DEPTNO = DEPT.DEPTNO;
IF EMPLOYEES < 2 THEN
DBMS_OUTPUT.PUT_LINE('There cannot be less than two employees per department');
END IF;
END;

I really don't know If I'm actually getting closer or far from it altogether...

Answer 1

which I'm sure they're simple

Actually these tasks are not simple for triggers. The business logic is simple, and the SQL to execute the business logic is simple, but implementing it in triggers is hard. To understand why you need to understand how triggers work.

Triggers fire as part of a transaction, which means they are are applied to the outcome of a SQL statement such as an insert or an update. There are two types of triggers, row level and statement level triggers.

Row-level triggers fire once for every row in the result set we can reference values in the current row, which is useful for evaluating row-level rules.. But we cannot execute DML against the owning table: Oracle hurls ORA-04088 mutating table exception, because such actions violate transactional integrity.

Statement level triggers fire exactly once per statement. Consequently they are useful for enforcing table-level rules but crucially they have no access to the result set, which means they don’t know which records have been affected by the DML.

Both your business rules are table level rules, as they require the evaluation of more than one EMP record. So, can we enforce them through triggers? Let’s start with the second rule:

there must not be less than two employees per department

We could implement this with an trigger AFTER statement trigger like this:

CREATE or replace TRIGGER MIN_LIMIT
AFTER DELETE OR UPDATE on EMP
declare
    EMPLOYEES pls_integer;
BEGIN

    for i in ( select * from dept) loop

        SELECT COUNT(EMPNO) INTO EMPLOYEES 
        FROM EMP
        where i.DEPTNO = EMP.DEPTNO;
        IF EMPLOYEES < 2 THEN
            raise_application_error(-20042, 'problem with dept #' || i.DEPTNO || '. There cannot be less than two employees per department');
        END IF;
    end loop;    
END;
/

Note this trigger uses RAISE_APPLICATION_ERROR() instead of DBMS_OUTPUT.PUT_LINE(). Raising an actual exception is always the best approach: messages can be ignored but exceptions must be handled.

The problem with this approach is that it will fail any update or delete of any employee, because the classic SCOTT.DEPT table has a record DEPTNO=40 which has no child records in EMP. So maybe we can be cool with departments which have zero employees but not with those which have just one?

CREATE or replace TRIGGER MIN_LIMIT
AFTER DELETE OR UPDATE on EMP
declare
    EMPLOYEES pls_integer;
BEGIN

    for i in ( select  deptno, count(*) as emp_cnt
                 from emp
                 group by deptno having count(*) < 2
               ) loop

             raise_application_error(-20042, 'problem with dept #' || i.DEPTNO || '. There cannot be less than two employees per department');
    end loop;    
END;
/

This will enforce the rule. Unless of course somebody tries to insert one employee into department 40:

insert into emp  
values(  2323, 'APC', ‘DEVELOPER', 7839,  sysdate,   4200, null, 40  )
/

We can commit this. It will succeed because our trigger doesn’t fire on insert. But some other user’s update will subsequently fail. Which is obviously bobbins. So we need to include INSERT in the trigger actions.

CREATE or replace TRIGGER MIN_LIMIT
AFTER INSERT or DELETE OR UPDATE on EMP
declare
    EMPLOYEES pls_integer;
BEGIN

    for i in ( select  deptno, count(*) as emp_cnt
                 from emp
                 group by deptno having count(*) < 2
               ) loop

             raise_application_error(-20042, 'problem with dept #' || i.DEPTNO || '. There cannot be less than two employees per department');
    end loop;    
END;
/

Unfortunately now we cannot insert one employee in department 40:

ORA-20042: problem with dept #40. There cannot be less than two employees per department
ORA-06512: at "APC.MIN_LIMIT", line 10
ORA-06512: at "SYS.DBMS_SQL", line 1721

We need to insert two employees in a single statement:

insert into emp  
select 2323, 'APC', 'DEVELOPER', 7839, sysdate, 4200, null, 40 from dual union all  
select 2324, 'ANGEL', 'DEVELOPER', 7839, sysdate, 4200, null, 40 from dual
/   

Note that switching existing employees to a new department has the same limitation: we have to update at least two employees in the same statement.

The other problem is that the trigger may perform badly, because we have to query the whole table after every statement. Perhaps we can do better? Yes. A compound trigger (Oracle 11g and later) allows us to track the affected records for use in a statement level AFTER trigger. Let’s see how we can use one to implement the first rule

No employee can have a salary higher than the 80% of their boss

Compound triggers are highly neat. They allow us to share program constructs across all the events of the trigger. This means we can store the values from row-level events in a collection, which we can use to drive some SQL in an statement level AFTER code..

So this trigger fires on three events. Before a SQL statement is processed we initialise a collection which uses the projection of the EMP table. The code before row stashes the pertinent values from the current row, if the employee has a manager. (Obviously the rule cannot apply to President King who has no boss). The after code loops through the stashed values, looks up the salary of the pertinent manager and evaluates the employee's new salary against their boss's salary.

CREATE  OR REPLACE TRIGGER MAX_SALARY 
FOR INSERT OR UPDATE ON EMP
COMPOUND TRIGGER
  type emp_array is table of emp%rowtype index by simple_integer;
  emps_nt emp_array ;
  v_idx simple_integer := 0;

BEFORE STATEMENT IS
BEGIN
    emps_nt := new emp_array();
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
    v_idx := v_idx + 1;
    if :new.mgr is not null then
        emps_nt(v_idx).empno := :new.empno;
        emps_nt(v_idx).mgr := :new.mgr;
        emps_nt(v_idx).sal := :new.sal;
    end if;
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
    null;
END AFTER EACH ROW;

AFTER STATEMENT IS
    mgr_sal emp.sal%type;
BEGIN
    for i in emps_nt.first() .. emps_nt.last() loop

         select sal into mgr_sal
         from emp 
          where emp.empno = emps_nt(i).mgr;

         if emps_nt(i).sal > (mgr_sal * 0.8) then
              raise_application_error(-20024, 'salary of empno ' || emps_nt(i).empno || ' is too high!');

        end if;

    end loop;

END AFTER STATEMENT;
END;
/

This code will check every employee if the update is universal, for instance when everybody gets a 20% pay rise...

update emp 
set sal = sal * 1.2
/

But if we only update a subset of the EMP table it only checks the boss records it needs to:

update emp set sal = sal * 1.2
where deptno = 20
/

This makes it more efficient than the previous trigger. We could re-write trigger MIN_LIMIT as a compound trigger; that is left as an exercise for the reader :)

Likewise, each trigger fails as soon as a single violating row is found:

ORA-20024: salary of empno 7902 is too high!
ORA-06512: at "APC.MAX_SALARY", line 36

It would be possible to evaluate all affected rows, stash the violating row(s) in another collection then display all the rows in the collection. Another exercise for the reader.

Finally, note that having two triggers fire on the same event on the same table is not good practice. It's generally better (more efficient, easier to debug) to have one trigger which does everything.

---

An after thought. What happens to Rule #1 if one session increases the salary of an employee whilst simultaneously another session decreases the salary of the boss? The trigger will pass both updates but we can end up with a violation of the rule. This is an inevitable consequence of the way triggers work with Oracle's read-commit transaction consistency. There is no way to avoid it except by employing a pessimistic locking strategy and pre-emptively locking all the rows which might be affected by a change. That may not scale and is definitely hard to implement using pure SQL: it needs stored procedures. This is another reason why triggers are not good for enforcing business rules.

---

I'm using Oracle10g

That is unfortunate. Oracle 10g has been obsolete for almost a decade now. Even 11g is deprecated. However, if you really have no option but to stick with 10g you have a couple of options.

The first is to grind through the whole table, doing the lookups of each boss for every employee. This is just about bearable for a toy table such as EMP but likely to be a performance disaster in real life.

The better option is to fake compound triggers using the same workaround we all used to apply: write a package. We rely on global variables - collections - to maintain state across calls to packaged procedures, and have different triggers to make those calls. Basically you need one procedure call for each trigger and one trigger for each step in the compound trigger. @JustinCave posted an example of how to do this on another question; it should be simple to translate my code above to his template.

Answer 2

Please handle these kind of validations/business logic at application or at DB level using procedures/functions instead of using triggers which most of the times slows down the DML operations/statements on which the triggers are based.

If you handle business logic at application or procedure level then, the DB server will have to execute only DML statements; it does not have to execute TRIGGER-executing trigger involves handling exceptions; prior to that DML statement will place a lock on the table on which DML (except for INSERT statement-Exclusive shared lock) is being executed until TRIGGER is executed.