PL/SQL trigger that doesn't allow insert if there's a name that is too similar already

Question

I've been trying to create an trigger in PL/SQL that doesn't allow new insertion if the name is too much similar to other name that already exists in the database, but I've been struggling to create this trigger (in insert, it does nothing even if the name is very similar), any ideas what I could do?

the code:

CREATE OR REPLACE TRIGGER similaridade
BEFORE INSERT ON pessoa
FOR EACH ROW
DECLARE
    similaridade_c NUMBER;
BEGIN
    SELECT COUNT(*) INTO similaridade_c
    FROM pessoa
    WHERE SIMILARITY(:NEW.nome, nome) > 0.92;
    
    IF similaridade_c > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Esse nome já existe');
    END IF;
END;

Answer 1

Unless you have a user-defined function called SIMILARITY then that function does not exist. You probably want one of the functions from the UTL_MATCH package:

CREATE OR REPLACE TRIGGER similaridade
BEFORE INSERT ON pessoa
FOR EACH ROW
DECLARE
    similaridade_c NUMBER;
BEGIN
    SELECT COUNT(*) INTO similaridade_c
    FROM   pessoa
    WHERE  UTL_MATCH.EDIT_DISTANCE_SIMILARITY(:NEW.nome, nome) > 0.92;
    
    IF similaridade_c > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Esse nome já existe');
    END IF;
END;
/

Then, if you have the table:

CREATE TABLE pessoa (nome) AS
SELECT 'Anna' FROM DUAL;

And you insert:

INSERT INTO pessoa (nome) VALUES ('Anya');

Then you get the error:

ORA-20001: Esse nome já existe
ORA-06512: at "FIDDLE_DOZWTHWYHQKNLDOVFQZI.SIMILARIDADE", line 9
ORA-04088: error during execution of trigger 'FIDDLE_DOZWTHWYHQKNLDOVFQZI.SIMILARIDADE'

fiddle

---

To fix the mutating table exception that will occur if you insert multiple rows you can change to using an AFTER INSERT statement-level trigger:

CREATE OR REPLACE TRIGGER similaridade
AFTER INSERT ON pessoa
DECLARE
    similaridade_c NUMBER;
BEGIN
    SELECT 1
    INTO   similaridade_c
    FROM   DUAL
    WHERE  NOT EXISTS(
             SELECT 1
             FROM   pessoa p
                    INNER JOIN pessoa s
                    ON  p.ROWID < s.ROWID
                    AND UTL_MATCH.EDIT_DISTANCE_SIMILARITY(p.nome, s.nome) > 0.92
           );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Esse nome já existe');
END;
/

Or you could use a compound trigger to only check the similarity of the inserted rows rather than checking the entire table (but that is left as an exercise to the reader to implement).

fiddle