甲骨文;限制每列列的行数

本文介绍了甲骨文;限制每列列的行数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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;