Oracle SQL: how to enforce only one value may be 'checked'?

Question

So I have a table in a database which contains the column "SELECTED". The values in this column can only be "CHECKED" or "UNCHECKED". I would like to enforce "CHECKED" can only be used once (like a radiobutton) through a PL/SQL trigger, though I cannot think of how to do this.

First, the idea (in case it didn't become clear):

Initial table "dummy":

ID | SELECTED
--------------
1  | 'UNCHECKED'
2  | 'CHECKED'
3  | 'UNCHECKED'

Then, I execute this query:

UPDATE dummy
SET SELECTED = 'CHECKED'
WHERE ID = 3;

Through a PL/SQL trigger, I'd like to have my table "dummy" to look like this after the execution:

ID | SELECTED
--------------
1  | 'UNCHECKED'
2  | 'UNCHECKED'
3  | 'CHECKED'

I hope you get the idea. I myself have tried to solve this, without success. I came up with the following code:

CREATE OR REPLACE TRIGGER DUMMY_ONE_CHECKED 
AFTER INSERT OR UPDATE ON DUMMY
FOR EACH ROW
DECLARE
  v_checked_is_present DUMMY.SELECTED%TYPE;
BEGIN
  SELECT SELECTED
  INTO v_checked_is_present
  FROM DUMMY
  WHERE SELECTED = 'CHECKED';

  IF v_checked_is_present IS NOT NULL THEN
    UPDATE DUMMY
    SET SELECTED = 'UNCHECKED'
    WHERE SELECTED = 'CHECKED';

    UPDATE DUMMY
    SET SELECTED = 'CHECKED'
    WHERE ID = :NEW.ID;
  END IF;
END;

However, I get the errors ORA-04091, ORA-06512 and ORA-04088 with the following message:

*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Clearly, this is not the right solution. I wonder how I could accomplish what I would like to do (if possible at all)?

Thank you in advance!

Answer 1

I would not design it that way. The database should enforce the rules, not automatically attempt to fix violations of them.

So, I'd enforce that only one row can be CHECKED at a time, like this:

CREATE UNIQUE INDEX dummy_enforce_only_one ON dummy ( NULLIF(selected,'UNCHECKED') );

Then, I'd make it the responsibility of calling code to deselect other rows before selecting a new one (rather than trying to have a trigger do it).

I know that doesn't answer the text of your question, but it does answer the title of your question: "how to enforce only one value..."