ABSTRACT
We often want to prevent the occurrence of a (closed) loop in data that has a hierarchical structure. Eg. in the famous Oracle table “emp” we want to prevent one employee from being the boss of another employee, and at the same time that other employee is (directly or indirectly) the boss of the first employee.
This requirement is, obviously, easy to define, and is quite common in practice. However, it is not easy to implement in a database. Here is a solution to this request in an Oracle database, without the help of a program on a client or application server.
The solution in single-user work is relatively simple (complicated by the problem of mutating tables). For the solution in multi-user work, the simulation of “ROLLBACK TO SAVEPOINT behavior” in the base trigger was applied, using quasi-remote procedures (procedures that we call as if they are on another base, even though they are in the local base)