Only allow same value in column after first Insert

Question

I am trying to create a trigger that only allows the user to insert the same value into a column after the first insert has been made. For example in the table below the user has entered Name = Jake and age = 29.

Name	Age
Jake	29

From this point onwards, the trigger on the name column should now only allow the user to enter the Name = Jake.

Name	Age
Jake	29
Jake	36

Code below is what I tried but I am getting a ORA-04091 error but I imagine this is because I am referencing the table itself in the trigger? Although I'm not sure

CREATE TRIGGER my_table
BEFORE INSERT OR UPDATE OF name ON my_table
FOR EACH ROW
DECLARE
a_CurrName VARCHAR2(20BYTE);
BEGIN
SELECT name INTO a_CurrName FROM my_table WHERE ROWNUM=1;
IF a_CurrName IS NOT NULL THEN
   IF :new.name != a_CurrName
   THEN
   RAISE_APPLICATION_ERROR( -20001, 'This table can only hold one unique name at any point' );
   END IF;
END IF;
END;

Answer 1

You can use a COMPOUND trigger:

CREATE TRIGGER my_table__same_name__trg
  FOR INSERT OR UPDATE OF name ON my_table
COMPOUND TRIGGER
  v_old_name  MY_TABLE.NAME%TYPE   := NULL;
BEFORE STATEMENT
  IS
  BEGIN
    SELECT name
    INTO   v_old_name
    FROM   my_table
    WHERE  ROWNUM=1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END BEFORE STATEMENT;
BEFORE EACH ROW
  IS
  BEGIN
    IF v_old_name IS NULL THEN
      v_old_name := :NEW.name;
    ELSIF v_old_name != :NEW.name THEN
      RAISE_APPLICATION_ERROR( -20001, 'This table can only hold one unique name at any point' );
    END IF;
  END BEFORE EACH ROW;  
END;
/

If you have the table:

CREATE TABLE my_table (
  name VARCHAR2(10) NOT NULL,
  age  NUMBER(3,0) NOT NULL,
  CONSTRAINT my_table__age_ge_0__chk CHECK ( age >= 0 )
);

Then:

INSERT INTO my_table (name, age) VALUES ('Alice', 26);

Works and afterwards:

INSERT INTO my_table (name, age) VALUES ('Betty', 27);

Raises the exception:

ORA-20001: This table can only hold one unique name at any point
ORA-06512: at "FIDDLE_ERLZSCAOAKYVIRYFNEFX.MY_TABLE__SAME_NAME__TRG", line 21
ORA-04088: error during execution of trigger 'FIDDLE_ERLZSCAOAKYVIRYFNEFX.MY_TABLE__SAME_NAME__TRG'

And trying to insert more rows with the same name works (both as single- and multi-row inserts):

INSERT INTO my_table (name, age) VALUES ('Alice', 30);

INSERT INTO my_table (name, age)
  SELECT 'Alice', 32 FROM DUAL UNION ALL
  SELECT 'Alice', 36 FROM DUAL;

And the table contains:

NAME	AGE
Alice	26
Alice	30
Alice	32
Alice	36

If you then try to insert multiple rows with different names into an empty table:

DELETE FROM my_table;

INSERT INTO my_table (name, age)
  SELECT 'Alice', 32 FROM DUAL UNION ALL
  SELECT 'Betty', 27 FROM DUAL;

Then you get the exception:

ORA-20001: This table can only hold one unique name at any point
ORA-06512: at "FIDDLE_ERLZSCAOAKYVIRYFNEFX.MY_TABLE__SAME_NAME__TRG", line 21
ORA-04088: error during execution of trigger 'FIDDLE_ERLZSCAOAKYVIRYFNEFX.MY_TABLE__SAME_NAME__TRG'

fiddle