With the new 11g feature Virtual Columns, we can (almost) declaratively implement dynamic constraints, such as:
- salaries can not be increased by more than 10%
- employee records can only be updated during business hours (9-5, Mon trough Fri)
- only a database user with the role HRM_MANAGER can change salaries
using a simple check constraint, a deterministic function and a single virtual column.
With normal check constraints, we have several limitations for implementing such constraints: we cannot use function calls, references to dynamic pseudocolumns like SYSDATE or USER or access the old value in case of an update.
So what do we do?
For each dynamic business rule we create:
- a deterministic function that performs the actual validation – possibly in an Autonomous Transaction to be able to refer to the old column values – and returning a varchar2 (Y or N)
- a virtual column with a meaningful name – for example SAL_RAISE_LIMIT; the virtual column is based on the function, passing in the required parameters such as rowid, column value
- a check constraint that specifies that the virtual column should have the value ‘Y’
Now for a real example:
Salaries may not be increased by more than 10%
We create a function to perform validation:
create or replace function sal_raise_check ( p_sal in number , p_empno in number ) return varchar2 deterministic is -- also required when used from deferred constraint?? pragma autonomous_transaction; l_old_sal number(10,2); begin select sal into l_old_sal from emp where empno = p_empno ; return case when nvl(p_sal,0) < 1.1 * nvl(l_old_sal,p_sal) then 'Y' else 'N' end; exception when no_data_found then return 'Y'; -- probably an insert, no problem with that end; /
We add a Virtual Column based on this function:
alter table emp ADD (sal_raise_chk AS (sal_raise_check(sal, empno))) /
Finally we add a check constraint on this Virtual Column:
alter table emp add constraint emp_sal_raise_chk check ( sal_raise_chk = 'Y') /
And now we check whether the Business Rule gets enforced:
SQL> update emp 2 set sal = sal * 1.05 3 / 14 rows updated. SQL> SQL> commit; Commit complete. SQL> SQL> update emp 2 set sal = sal * 1.15 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_SAL_RAISE_CHK) violated SQL> SQL> commit; Commit complete. SQL> SQL> update emp 2 set sal = sal + 500 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_SAL_RAISE_CHK) violated SQL> SQL> commit; Commit complete. SQL> SQL> update emp 2 set sal = sal* 1.08 3 / 14 rows updated. SQL> SQL> update emp 2 set sal = sal* 1.07 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_SAL_RAISE_CHK) violated SQL> SQL> commit 2 / Commit complete. SQL> SQL> update emp 2 set sal = sal* 1/1.05 3 / 14 rows updated. SQL> SQL> commit 2 / Commit complete.
You see how we can enforce a dynamic business rule using a declarative (check ) constraint!
(most parts of) Employee records can only be updated during business hours
Another dynamic Business Rule – it only needs to be validated during the DML operation, afterwards – once the data has been committed – it becomes meaningless.
Again we create a function:
create or replace function bus_hour_check ( p_sal in number , p_ename in varchar2 , p_hiredate in date , p_deptno in number , p_mgr in number ) return varchar2 deterministic is begin return case when to_char(sysdate, 'HH24') between 9 and 17 and to_char(sysdate, 'D') between 2 and 6 then 'Y' else 'N' end; end; /
Note how JOB is not in the list of parameters: JOB is not bound to the rule to allow for impromptu promotions.
And a Virtual Column based on the function:
alter table emp ADD (bus_hour_chk AS (bus_hour_check(sal, ename,hiredate,deptno,mgr))) /
And a Check Constraint:
rem can add Check Constrain only during business hours - or with no validate alter table emp add constraint emp_bus_hour_chk check ( bus_hour_chk = 'Y') NOVALIDATE /
And now we test the implementation – I tested at 7AM, so clearly outside the business hours specified in the business rule.
SQL> update emp 2 set sal = sal * 1.05 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_BUS_HOUR_CHK) violated SQL> SQL> SQL> update emp 2 set ename = lower(ename) 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_BUS_HOUR_CHK) violated SQL> SQL> commit; Commit complete. SQL> SQL> update emp 2 set job = 'BOSS' 3 where job = 'MANAGER' 4 / 0 rows updated. SQL> rem set time to 11AM SQL> ALTER SYSTEM SET fixed_date = '2007-10-05-11:00:00'; System altered. SQL> update emp 2 set ename = lower(ename) 3 / 14 rows updated. SQL> ALTER SYSTEM SET fixed_date = NONE; SQL> update emp 2 set ename = lower(ename) 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_BUS_HOUR_CHK) violated
Again, using a function, virtual column and check constraint, I could easily implement the dynamic check constraint.
Authorization Rule: only a database user with the role HRM_MANAGER can change salaries
The last example of a dynamic business rule – this time not WHEN or WHAT is changed but WHO:a change in a salary value is only allowed if and when the user making the change has the database role HRM_MANAGER.
You know the drill by now:
create or replace function hrm_manager_check ( p_sal in number ) return varchar2 deterministic is l_count number(2); begin select count(*) into l_count from session_roles where role ='HRM_MANAGER' ; if dbms_session.is_role_enabled('HRM_MANAGER') then return 'Y'; else return 'N'; end if; end; /
Create a virtual column
alter table emp ADD (hrm_manager_chk AS (hrm_manager_check(sal))) /
Create a check constraint
alter table emp add constraint emp_hrm_manager_chk check ( hrm_manager_chk = 'Y') NOVALIDATE /
Do the test:
SQL> update emp 2 set sal = sal * 1.05 3 / update emp * ERROR at line 1: ORA-02290: check constraint (SCOTT.EMP_HRM_MANAGER_CHK) violated SQL> update emp 2 set ename = lower(ename) 3 / 14 rows updated. SQL> commit; Commit complete. SQL> create role hrm_manager 2 / Role created. SQL> grant hrm_manager to scott 2 / Grant succeeded. SQL> set role all 2 / Role set. SQL> select * from session_roles 2 / ROLE ------------------------------ CONNECT RESOURCE HRM_MANAGER 3 rows selected. SQL> update emp 2 set sal = sal * 1.05 3 / 14 rows updated.
And once again we find that a dynamic rule – referring to the WHO behind the change – at this moment, the moment of making the change – can be enforced in a declarative way.
Conclusions
Should we rush in now – once you get to 11g – and start implementing all our Dynamic Business Rules using Virtual Columns and Check Constraints? Probably not a very good idea. One disadvantage of using the Virtual Column is that it clutters up the DESC TABLE – table meta data. What the other consequences are for performance, maintenance and administration are not clear to me yet. What I do want to achieve is for you to see what can ben done. And what perhaps may be valuable to you.
Note: the functions now used in the Virtual Column definitions can of course be used from row-level table triggers as well. The potential advantage from using Check Constraints should come from benefits in the above mentioned areas of performance, maintenance and administration.
Lucas, interesting post. After reading your previous post about virtual columns I had some similar thoughts about using virtual columns for business rule validation.
What about just adding a generic RECORD_IS_VALID virtual column to a table? With – as you suggest – a check constraint to ensure that the value of the virtual column is always “Y”.
If we base the value of that virtual column on a “validate_record” function that accepts the values of all “real” columns of the table, this “validate_record” function could be a generic “hook” to other functions that do the actual checking of individual rules. A kind of alternative framework for business rule checking, that does not clutter up table definitins too much. Just some thoughts…
Regards,
Eric