Oracle; limit rows per column value

问题:

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?

回答1:

This may work instead of your trigger:

CREATE MATERIALIZED VIEW mv_my
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT POSTALCODE, COUNT(*) AS cnt FROM TEST GROUP BY POSTALCODE
/

ALTER TABLE mv_my ADD CHECK(cnt < 2)
/

Let me know. Cheers,

Sebas

回答2:

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;