1. On the application server
   
2. On the database server

If you don't have Oracle RuleFrame, you can try to make your own implementation of the business rules on the database. But there are some business rules that are not entirely supported by the database without the cooperation of the client side.

Probably the simplest example of that type of rule is: "Instance of Master must have at least one depending instance of Detail." (e.g. "DEPT has to have at least one EMP."). Those rules could be called "COMMIT rules", because they can be evaluated only before committing the transaction, and not at the execution of some DML command. Since the Oracle database does not have (and probably never will have) any sort of BEFORE COMMIT trigger, (which would help solve the "COMMIT rules" issue), you can try to resolve the issue by using one of the following two methods:

1. Solving "COMMIT rules" without Deferred Constraint Checking

One possible solution on Oracle Version 7 is to add a field like br_valid (br for Business Rule) to the Master table and to use database triggers to ensure that the row that has the value of "T" (True) for the br_valid field complies with the "COMMIT rule".

For example, you add the br_valid field to the DEPT table and fill it with the correct value at the beginning:

ALTER TABLE dept
   ADD (br_valid CHAR (1) DEFAULT 'F' NOT NULL,
        CONSTRAINT dept_br_valid_ck CHECK (br_valid IN ('T', 'F')))
/
UPDATE dept
   SET br_valid = 'T'
 WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno)
/

CREATE OR REPLACE TRIGGER bir_dept
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
   :NEW.br_valid := 'F';
END;
/
CREATE OR REPLACE TRIGGER bur_dept
BEFORE UPDATE ON dept
FOR EACH ROW
BEGIN
   IF :OLD.deptno <> :NEW.deptno THEN
      RAISE_APPLICATION_ERROR (-20001, 'Can''t change deptno in DEPT!');
   END IF;

   IF :OLD.br_valid = 'F' AND :NEW.br_valid = 'T' THEN
      DECLARE
         l_dummy NUMBER (1, 0);
         CURSOR c_emp IS
            SELECT 1 FROM emp WHERE deptno = :OLD.deptno;
      BEGIN
         OPEN c_emp;
         FETCH c_emp INTO l_dummy;
         IF c_emp%NOTFOUND THEN
            CLOSE c_emp;
            RAISE_APPLICATION_ERROR (-20002, 'DEPT must have EMPs!');
         END IF;
         CLOSE c_emp;
      END;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER aur_emp
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
   IF NVL (:OLD.deptno, 0) <>  NVL (:NEW.deptno, 0) THEN
      UPDATE dept
         SET br_valid = 'F'
       WHERE deptno = :OLD.deptno
         AND br_valid = 'T';
   END IF;
END;
/
CREATE OR REPLACE TRIGGER adr_emp
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
   UPDATE dept
      SET br_valid = 'F'
    WHERE deptno = :OLD.deptno
      AND br_valid = 'T';
END;
/

UPDATE dept
   SET br_valid = 'T'
 WHERE deptno := :dept.deptno
   AND br_valid = 'F';

2. Solving "COMMIT rules" with Deferred Constraint Checking (possible on Oracle Version 8.0 and Later)

The method that allows you to solve "COMMIT rules" completely on the database level is the ability to delay the checking of the declarative constraints (NOT NULL, Primary Key, Unique Key, Foreign Key, Check Constraints) until the commit. That method was introduced first in Oracle 8.0. It is important to note that the execution of the triggers cannot be delayed.

For example, you add the field "num_emps" to the DEPT table, which always has the value of the number of the belonging EMP rows, and add DEFERRED CK which uses the values from that field:

ALTER TABLE dept ADD num_emps NUMBER DEFAULT 0 NOT NULL
/
UPDATE dept
   SET num_emps = (SELECT COUNT (*) FROM emp WHERE emp.deptno = dept.deptno)
/
DELETE dept WHERE num_emps = 0 
/
ALTER TABLE dept ADD CONSTRAINT dept_num_emps_ck CHECK (num_emps > 0) INITIALLY DEFERRED
/

CREATE OR REPLACE PACKAGE pack IS
   PROCEDURE set_flag;
   PROCEDURE reset_flag;
   FUNCTION dml_from_emp RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY pack IS
   m_dml_from_emp BOOLEAN := FALSE;

   PROCEDURE set_flag IS
   BEGIN
      m_dml_from_emp := TRUE;
   END;

   PROCEDURE reset_flag IS
   BEGIN
      m_dml_from_emp := FALSE;
   END;

   FUNCTION dml_from_emp RETURN BOOLEAN IS
   BEGIN
      RETURN m_dml_from_emp;
   END;
END;
/

CREATE OR REPLACE TRIGGER bir_dept
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
   :NEW.num_emps := 0;
END;
/
CREATE OR REPLACE TRIGGER bur_dept
BEFORE UPDATE ON dept
FOR EACH ROW
BEGIN
   IF :OLD.deptno <> :NEW.deptno THEN
      RAISE_APPLICATION_ERROR (-20001, 'Can''t change deptno in DEPT!');
   END IF;

   -- only EMP trigger can change "num_emps" column
   IF NOT pack.dml_from_emp THEN
      :NEW.num_emps := :OLD.num_emps;
   END IF;
END;
/ 
CREATE OR REPLACE TRIGGER air_emp
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
   pack.set_flag;

   UPDATE dept
      SET num_emps = num_emps + 1
    WHERE deptno = :NEW.deptno;

   pack.reset_flag;
END;
/
CREATE OR REPLACE TRIGGER aur_emp
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
   IF NVL (:OLD.deptno, 0) <>  NVL (:NEW.deptno, 0) THEN
      pack.set_flag;

      UPDATE dept
         SET num_emps = num_emps - 1
       WHERE deptno = :OLD.deptno;

      UPDATE dept
         SET num_emps = num_emps + 1
       WHERE deptno = :NEW.deptno;

      pack.reset_flag;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER adr_emp
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
   pack.set_flag;

   UPDATE dept
      SET num_emps = num_emps - 1
    WHERE deptno = :OLD.deptno;

   pack.reset_flag;
END;
/

ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.DEPT_NUM_EMPS_CK) violated

Note that in both of the solutions above you had to add "auxiliary" columns in the DEPT table ("br_valid" in the first case, and "num_emps" in the second). In the second case only one "auxiliary" column is needed for each "COMMIT rule".

If you wish like to add another "COMMIT rule" to the DEPT table, such as:

SUM (sal) FROM emp WHERE deptno = p_deptno must be <= p_max_dept_sal

in the first solution we could use the same "br_valid" column for both the rules, but in the second case we would have to add another column, like "dept_sal". Another Oracle CDM RuleFrame advantage is that is does not force us to add "auxiliary" columns.

It is important to note that in real life you would not write PL/SQL code directly in the database triggers, but in packages, nor would you directly use RAISE_APPLICATION_ERROR. It is written this way in this sample only for the purposes of clarity.