Prevent delete from table if only one entry

Question

I want to prevent deletion of a phone number if there is only one in the table at the time.

So far I have this trigger:

CREATE OR REPLACE TRIGGER T_TfnoCliente_Cliente
BEFORE DELETE ON TFNO_CLIENTE
FOR EACH ROW
DECLARE
--PRAGMA AUTONOMOUS_TRANSACTION;
telefonosClienteAtencion NUMBER;
BEGIN
telefonosClienteAtencion := 0;
SELECT count(1) INTO telefonosClienteAtencion FROM TFNO_CLIENTE WHERE id = :old.id AND tipo = :old.tipo;
IF telefonosClienteAtencion < 2 THEN
  RAISE_APPLICATION_ERROR(-20101, 'Cannot delete a phone number if the user doesn't have more than one phone associated');
END IF;
END;

I am getting error ORA-04091: the table TFNO_CLIENT is mutating (...)

I understand the issue, and I have tried several alternatives:

1. Pragma autonomous_transaction. This causes malfunctions and I would like to avoid it as much as possible.

2. Instead of querying the table itself, querying a view that holds the count:

I then changed the query to

SELECT no_tfnos INTO telefonosClienteAtencion FROM CLIENTES_TFNOS WHERE cliente = :old.id;

CREATE OR REPLACE VIEW CLIENTES_TFNOS (cliente, no_tfnos) AS
SELECT id, count(*) 
from TFNO_CLIENTE 
group by id;

This didn't work either.

Is there any way to solve this easily? Thanks!

Answer 1

This should be considered pseudo code

FUNCTION FN_HasMoreThanOnePhoneNumber(vclient_id IN NUMBER, vphone IN VARCHAR2) 
RETURN BOOLEAN IS
telefonosClienteAtencion NUMBER;
vResult BOOLEAN := FALSE;
BEGIN
telefonosClienteAtencion := 0;
SELECT count(*) 
INTO telefonosClienteAtencion 
FROM TFNO_CLIENTE WHERE id = vclient_id 
AND tipo = vphone;

IF telefonosClienteAtencion > 1 THEN
  vResult := TRUE;
END IF;

RETURN vResult;

END FN_HasMoreThanOnePhoneNumber;

Then in your code where you make the decision to insert or delete it could work like this

IF FN_HasMoreThanOnePhoneNumber(lclientId, lPhone) THEN
   process new data by deleting old phone number
ELSE
   RAISE_APPLICATION_ERROR(-20101, 'Cannot delete a phone number if the user doesn't have 
   more than one phone associated');
END IF;

A trigger hides your business logic. By having a function that does one thing and only one thing you can make what you want clear to the next programmer who looks at your work.