甲骨文;限制每列列的行数 [英] Oracle; limit rows per column value

问题描述

  CREATE TABLETEST 
（
IDNUMBER（10）NOT NULL ENABLE，
POSTALCODEVARCHAR2（6）NOT NULL ENABLE，
 .... 
 CONSTRAINTTEST_PK PRIMARY KEY（ID）ENABLE 
）
  

我正在尝试创建一个触发器限制具有重复邮政编码的记录数量;只有两行可能同时包含相同的邮政编码。
我尝试了以下代码：

  CREATE OR REPLACE TRIGGER TR_TEST_PC 
在POSTALCODE更新或插入之前ONTEST
 FOR EACH ROW 
 WHEN（OLD.POSTALCODE<> NEW.POSTALCODE）
 DECLARE 
 v_count NUMBER; 
 BEGIN 
 SELECT COUNT（*）
 INTO v_count 
 FROM TEST 
 WHERE POSTALCODE =：NEW.POSTALCODE; 
 IF（v_count> 2）THEN 
 raise_application_error（-20000，'最多2行，具有相同的邮政编码'）; 
 END IF; 
 END TR_TEST_PC; 
  

但这会返回错误：

  ORA-04091：表MYDB.TEST是突变，触发器/功能可能看不到
 ORA-06512：在MYDB.TR_TEST_PC，第4行
 ORA- 04088：执行触发器时出错'MYDB.TR_TEST_PC'
  

我明白这是因为你不能访问您变异的表。

如何创建一个触发器，确保不超过2个记录包含相同的邮政编码？

 创建或替换触发器TR_TEST_PC 
测试后的POSTALCODE更新或插入
 DECLARE 
 v_count NUMBER; 
 BEGIN 
 SELECT MAX（COUNT（*））
 INTO v_count 
 FROM TEST 
 GROUP BY POSTALCODE; 
 IF（v_count> 2）THEN 
 raise_application_error（-20000，'最多2行，具有相同的邮政编码'）; 
 END IF; 
 END TR_TEST_PC; 
  

I have a table with a postal code column (Dutch format, but never mind that)

CREATE TABLE  "TEST"
(
    "ID" NUMBER(10) NOT NULL ENABLE,
    "POSTALCODE" VARCHAR2(6) NOT NULL ENABLE,
    ....
    CONSTRAINT "TEST_PK" PRIMARY KEY ("ID") ENABLE
)

I'm trying to create a trigger that limits the number of records with duplicate postal codes; only two rows may contain the same postal code at once. I tried the following code:

CREATE OR REPLACE TRIGGER TR_TEST_PC
    BEFORE UPDATE OR INSERT OF "POSTALCODE " ON "TEST"
    FOR EACH ROW
    WHEN ( OLD.POSTALCODE <> NEW.POSTALCODE )
DECLARE
   v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM TEST
    WHERE POSTALCODE = :NEW.POSTALCODE ;  
    IF (v_count > 2) THEN
        raise_application_error(-20000, 'Max. 2 rows with same postal code.');
    END IF;
END TR_TEST_PC;

But this returns an error:

ORA-04091: table MYDB.TEST is mutating, trigger/function may not see it
ORA-06512: at "MYDB.TR_TEST_PC", line 4
ORA-04088: error during execution of trigger 'MYDB.TR_TEST_PC'

I understand this is because you can't access a table you're mutating.

How can I make a trigger that ensure no more than 2 records contain the same postal code?

解决方案

I ended up using an after-update trigger instead:

CREATE OR REPLACE TRIGGER TR_TEST_PC
    AFTER UPDATE OR INSERT OF "POSTALCODE" ON "TEST"
DECLARE
    v_count NUMBER;
BEGIN
    SELECT MAX(COUNT(*))
    INTO v_count
    FROM TEST
    GROUP BY POSTALCODE;
    IF (v_count > 2) THEN
        raise_application_error(-20000, 'Max. 2 rows with same postal code.');
    END IF;
END TR_TEST_PC;

这篇关于甲骨文;限制每列列的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！