Sayantan's Blog On Oracle

Trigger for preventing insertion of duplicate values in a table in Oracle

We can use trigger to restrict insertion of duplicate values in a table in Oracle.

But there is a challenge to create trigger on the table itself.

Oracle will throw Mutating table error as we are querying the same table in which data is inserting.

To avoid this error we will use an instead of trigger.

Creating a test table to test this:

--Trigger for preventing insertion of duplicate values in a table in Oracle : Setup

CREATE TABLE EMP_TRIG_TEST
(
 EMPLOYEE_ID            NUMBER(6),    
 FIRST_NAME             VARCHAR2(20), 
 LAST_NAME              VARCHAR2(25), 
 SALARY                 NUMBER(8,2),  
 HIRE_DATE              DATE,         
 DEPARTMENT_ID          NUMBER(4)
);

Creating a view for instead of trigger:

--Trigger for preventing insertion of duplicate values in a table in Oracle : Setup

CREATE OR REPLACE VIEW VW_EMP_TRIG_TEST
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       HIRE_DATE,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

We will create an instead trigger on the view VW_EMP_TRIG_TEST which will insert record into table EMP_TRIG_TEST instead of inserting the same on view.

Creating an instead of trigger to enable duplicate check:

--Trigger for preventing insertion of duplicate values in a table in Oracle : Setup

CREATE OR REPLACE TRIGGER TRIG_DUPLICATE_CHECK
INSTEAD OF INSERT OR UPDATE ON VW_EMP_TRIG_TEST
FOR EACH ROW
DECLARE
  V_COUNT NUMBER := 0;
BEGIN
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_TRIG_TEST
  WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
  IF V_COUNT > 0 THEN
    RAISE_APPLICATION_ERROR(-20202, 'EMPLOYEE '||:NEW.EMPLOYEE_ID||' IS ALREADY EXISTS. PLEASE CHECK.');
  ELSE
    IF INSERTING THEN
      INSERT INTO EMP_TRIG_TEST(EMPLOYEE_ID,
                                FIRST_NAME,
                                LAST_NAME,
                                SALARY,
                                HIRE_DATE,
                                DEPARTMENT_ID
                               )
      VALUES(:NEW.EMPLOYEE_ID,
             :NEW.FIRST_NAME,
             :NEW.LAST_NAME,
             :NEW.SALARY,
             :NEW.HIRE_DATE,
             :NEW.DEPARTMENT_ID
            );
    ELSIF UPDATING THEN
      UPDATE EMP_TRIG_TEST
      SET EMPLOYEE_ID = :NEW.EMPLOYEE_ID,
          FIRST_NAME = :NEW.FIRST_NAME,
          LAST_NAME = :NEW.LAST_NAME,
          SALARY = :NEW.SALARY,
          HIRE_DATE = :NEW.HIRE_DATE,
          DEPARTMENT_ID = :NEW.DEPARTMENT_ID
      WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
    END IF;
  END IF;
END;
/

INSERT INTO VW_EMP_TRIG_TEST(EMPLOYEE_ID,
                          FIRST_NAME,
                          LAST_NAME,
                          SALARY,
                          HIRE_DATE,
                          DEPARTMENT_ID
                         )
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       HIRE_DATE,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

7 rows inserted.

SELECT * FROM EMP_TRIG_TEST;

Trying to insert a duplicate record:

--Trigger for preventing insertion of duplicate values in a table in Oracle : Example

INSERT INTO VW_EMP_TRIG_TEST(EMPLOYEE_ID,
                          FIRST_NAME,
                          LAST_NAME,
                          SALARY,
                          HIRE_DATE,
                          DEPARTMENT_ID
                         )
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       HIRE_DATE,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
AND EMPLOYEE_ID = 208;

--Trigger for preventing insertion of duplicate values in a table in Oracle : Output

Error report -
SQL Error: ORA-20202: EMPLOYEE 208 IS ALREADY EXISTS. PLEASE CHECK.
ORA-06512: at "HR.TRIG_DUPLICATE_CHECK", line 9
ORA-04088: error during execution of trigger 'HR.TRIG_DUPLICATE_CHECK'

Doing the testing on updation:

UPDATE VW_EMP_TRIG_TEST
SET EMPLOYEE_ID = 114
WHERE EMPLOYEE_ID = 208;

--Trigger for preventing insertion of duplicate values in a table in Oracle : Output

Error report -
SQL Error: ORA-20202: EMPLOYEE 114 IS ALREADY EXISTS. PLEASE CHECK.
ORA-06512: at "HR.TRIG_DUPLICATE_CHECK", line 9
ORA-04088: error during execution of trigger 'HR.TRIG_DUPLICATE_CHECK'

RELATED TOPICS:

Working With DML TRIGGERS
Working With Triggers Other Than DML Triggers
FOLLOWS Keyword & COMPOUND TRIGGER
WORKING WITH PLSQL EXCEPTION