如何在Oracle SQL中添加检查总和的约束？
如何在Oracle SQL中添加检查总和的约束？
提问于 2019-03-27 07:08:10
我目前正在做一个学校项目，在这个项目中我们需要为一家房地产管理公司创建一个数据库。我们有一个OWNER表、一个BUILDING表和一个OWNERSHIP表。
我希望确保当我输入所有权股权百分比的值时，来自不同所有者的所有所有权股权的总和不会超过100%。目前我还不知道该怎么做。
CREATE TABLE Building (
buildingID NUMBER (10) NOT NULL PRIMARY KEY,
qtyUnits NUMBER (3) NOT NULL,
landValue NUMBER (15) NOT NULL,
purchasePrice NUMBER (15) NOT NULL
);
CREATE TABLE Owners (
ownerID NUMBER (5) NOT NULL PRIMARY KEY,
lastName VARCHAR2 (50) NOT NULL,
firstName VARCHAR2 (50) NOT NULL,
telephone VARCHAR2(50) NOT NULL,
email VARCHAR2(10) NOT NULL
);
CREATE TABLE Ownership (
ownerID NUMBER (5) NOT NULL,
buildingID NUMBER (5) NOT NULL,
ownershipStake NUMBER (5,2) NOT NULL,
CONSTRAINT PK_Ownership PRIMARY KEY (ownerID,buildingID)
);回答 2
Stack Overflow用户
发布于 2019-03-27 10:49:11
所有与触发器相关的解决方案都有一个共同的问题:一旦系统中有多个用户，它们就不足以保证约束得到遵守。例如，如果会话A插入的ownershipshare为51%，会话B插入的ownershipshare为51%，则这两个插入都将成功，因为两个会话都没有提交。然后两个会话都提交，您将拥有102%的总ownershpshare。
解决此问题的一种方法是使用带约束的ON COMMIT物化视图。不幸的是，我认为物化视图是仅在Oracle Enterprise Edition中可用的特性，而不是Standard或Express中提供的特性。我没有可以测试的EE实例，但我认为这就是您想要的：
create materialized view log on ownership
with primary key, rowid, sequence
( ownershipstake )
including new values;
create materialized view mv_ownership
refresh fast on commit
as
select buildingid, sum(ownershipstake) as total_ownershipstake, count(*) as count_ownershipstake
from ownership
group by buildingid;
alter materialized view mv_ownership add (
constraint ck_100 check ( total_ownershipstake <= 100 )
); 我做了一些额外的工作来使物化视图可快速刷新，这样整个事情就不需要在每次提交时重新构建，只需重新构建受影响的buildingid即可。
Stack Overflow用户
发布于 2020-05-07 20:29:32
使用复合触发器
CREATE OR REPLACE TRIGGER IVAN.trades_partial_kontrola_tg
FOR INSERT OR UPDATE OR DELETE ON ivan.trades_partial
COMPOUND TRIGGER
cNic CONSTANT NUMBER(10) := -9999999999;
--CREATE OR REPLACE TYPE IVAN.NUMBER_POLE_TYP as table of number;
lPole ivan.number_pole_typ := ivan.number_pole_typ();
lPole2 ivan.number_pole_typ;
lAmountTrades ivan.trades.amount%TYPE;
lAmountPartial ivan.trades_partial.amount%TYPE;
BEFORE EACH ROW IS
BEGIN
CASE
WHEN updating
AND :new.amount = :old.amount THEN
NULL;
WHEN nvl(:new.amount, cNic) <> nvl(:old.amount, cNic) THEN
lPole.extend();
lPole(lPole.last()) := nvl(:new.id, :old.id);
END CASE;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
SELECT DISTINCT column_value BULK COLLECT INTO lPole2 FROM TABLE(lPole);
lPole.delete;
FOR a_cur IN (SELECT * FROM TABLE(lPole2))
LOOP
SELECT t.amount INTO lAmountTrades FROM ivan.trades t WHERE t.id = a_cur.column_value;
SELECT SUM(a.amount) INTO lAmountPartial FROM ivan.trades_partial a WHERE a.id = a_cur.column_value;
IF lAmountPartial <> lAmountTrades
THEN
ivan.log_centralni_pk.myraise('Wrong amount check');
END IF;
END LOOP;
END AFTER STATEMENT;
end;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55367470
复制相关文章
相似问题