数据库表级别的约束，而不是函数

Question

我有下面的select语句，我正在一个函数中使用它来检查记录的重叠，这个函数是从应用程序前端调用的。

SELECT count(*),product_no 
from products p where lower(p.PRODUCT_REG_NO) ='GB-UNIGAS' 
and (NVL (p.return_date, p.end_date) >= '01-Jan-2015') 
and p.product_no in (select product_no from PRODUCT_MASTER  where  EXPIRED='N' 
and product_no  = p.product_no)

我想在表中创建一个约束，这样即使在数据库级别也不会有任何插入或更新，而不是检查记录重叠的函数。

如何使用上面的sql语句创建约束？

任何帮助都是非常可观的。

感谢

Answer 1

您可以创建Before Insert或更新触发器，检查您的条件，如果新数据不符合您的要求，则会引发错误。此链接将对您有所帮助。

Answer 2

在存储过程中保留这种逻辑是一个不错的选择。尝试在触发器中实现该逻辑，以某种方式，你最终会看到

ORA-04091: table <your table> is mutating, trigger/function may not see it

例如，在触发器中实现了这一点，将允许您像一样使用insert

insert into <Yourtable> (<col1>...<coln>)
values(val1... valn)

但是如果你尝试像这个一样执行insert，你肯定会得到mutating table错误

insert into <Yourtable> (<col1>...<coln>)
select col1, .. coln 
from some_table

如果您不想使用存储过程，您的另一个选择可能是view和instead of trigger

Create or replace view <ViewName> as
select *
from your_table;
create or replace trigger <Trigger_name> 
instead of insert on <View_Name>
begin
-- your logic
end;

附加

此外，为了通过约束(CHECK约束)强制执行业务规则，可以将CHECK约束和materialized view组合如下：为查询违反业务规则约束的数据的表创建物化视图(不要忘记之前创建materialized view log)。

Create materialized view MV_1
refresh complete on commit as
--here goes your query
-- selecting data you want to raise exception on

添加始终为false的check约束。像这样的

alter table MV_1 add constraint CHK check(1=2) deferrable;

这样，当您试图在表中插入违反业务规则约束的数据时，您将得到check constraint <..> violated。