using triggers for ensuring data consistency

Question

I have a hierarchical structure stored in a table. Each element has a pointer to its previous, next and a parent

create table CATALOGUE
(
  NAME VARCHAR2(300) not null,
  NEXT_ID NUMBER(38),
  PARENT_ID NUMBER(38),
  PREVIOUS_ID NUMBER(38),
  XID NUMBER(38)
);

I have a java application, which uses O/R mapping to access and modify this table. Sometimes my catalog got corrupted, e.g. they are linked elements which don't have the same parent. I'm wondering if I can ensure data consistency using Oracle triggers or other pure SQL technique (without java code).

Is this "the correct way" of doing things?

How can I implement a trigger? I can implement a stored procedure which would validate my table. Something like

select count(*) 
from catalogue c1, catalogue c2 
where c1.next_id = c2.previous_id and c1.parent_id != c2.parent_id

should return 0.

But how can I call it on commit? I don't want to call it on every row update, just before the commit is finished with a possibility to rollback if my table is not valid.

Answer 1

It may be possible to enforce this by a combination of a materialized view (MV) and a constraint on the MV as I have described here in my blog.

The idea would be to create an MV that held only exceptions to the rule, and then to have a constraint that always fails when a row is entered into the MV. Something like this:

create materialized view check_mv
refresh complete on commit as
select 1 dummy
from catalogue c1, catalogue c2 
where c1.next_id = c2.previous_id and c1.parent_id != c2.parent_id

alter table check_mv
add constraint check_mv_chk
check (1=0) deferrable;

Answer 2

Ideally, you should write a package that is 100% in control of maintaining this table. If necessary, put it in it's own schema, lock down privileges on it, and use ONLY THIS PACKAGE to modify the table.