使用触发器来确保数据一致性 [英] using triggers for ensuring data consistency
问题描述
我有一个存储在表中的层次结构.每个元素都有一个指向其上一个,下一个和父级的指针
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)
);
我有一个Java应用程序,它使用O/R映射来访问和修改此表.有时我的目录损坏了,例如它们是没有相同父项的链接元素.我想知道是否可以使用Oracle触发器或其他纯SQL技术(没有Java代码)确保数据一致性.
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
应返回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.
推荐答案
通过将物化视图(MV)和对MV的约束结合使用,可以强制执行此操作,如我所描述的
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.
该想法将是创建一个仅包含规则异常的MV,然后创建一个约束,该约束在将一行输入到MV中时始终会失败.像这样:
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;
这篇关于使用触发器来确保数据一致性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!