本文介绍了甲骨文;限制每列列的行数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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;
更多推荐
甲骨文;限制每列列的行数
发布评论