JavaEar 专注于收集分享传播有价值的技术资料

Ora 06512/04088 triggers errors when INSERT INTO statement

I'm at work for a trigger which provide a "domain" for column Molteplicità in a table called Partecipa using a function.

The trigger I've created is the following:

CREATE OR REPLACE TRIGGER dominioMolteplicità
BEFORE INSERT OR UPDATE ON partecipa
FOR EACH ROW
BEGIN
    IF moltepl_valido(:NEW.molteplicità) = 'f' THEN
        RAISE_APPLICAZION_ERROR(-20002, 'Invalid type');
    END IF;
END;

which uses the following function:

CREATE OR REPLACE FUNCTION motepl_valido(mol VARCHAR2) RETURN CHAR IS
BEGIN
    IF regexp_like(LOWER(mol), ' [*]\..[*] ') THEN
        RETURN 't';
    ELSE
        RETURN 'f';
    END IF;
END;

Table Partecipa has the following columns:

CodP INT,
molteplicità VARCHAR2,
codAss INT,
className VARCHAR2,
PRIMARY KEY (codP),
FOREIGN KEY (className) REFERENCES class(name),
FOREIGN KEY (codAss) REFERENCES associazione(cod)`

and even though in my Associazione table there are rows (in particular codaAss: 42) and in my Class table there are rows (in particular className: 'Impiegato')

When I execute the following statement

insert into Partecipa(molteplicità, className, codAss) 
values ('*..*', 'Impiegato', 42);

I get these errors:

ORA-20002 INVALID TYPE
ORA-06512: AT "dominioMolteplicità", line 3
ORA-04088: ERROR DURING EXECUTION OF TRIGGER "dominioMolteplicità"

(Note that if I disable my trigger, the insert statement works properly. There's some problem with the trigger, but I can't find the mistake.)

SOLVED:

Removing the spaces in the regexp

2个回答

    最佳答案
  1. Beacue your pattern doesn't conform your data

    I suppose regexp_like(LOWER(mol), '*..*') would be alright, and in this case the values such as '*=-*' or '*34*' for molteplicità would work.

    Btw, even '[*]..[*]' or '[\*]..[\*]'(where backslash used as an escape character) might be used as pattern for the above regular expression.

  2. 参考答案2
  3. It's not related to the trigger.

    Your function motepl_valido raises ORA-20002 INVALID TYPE if the supplied string (in this case '*..*') does not match the regex ' [*]\..[*] '. It doesn't match because it's missing the required spaces.