CREATE OR REPLACE TRIGGER chk_ip_duplications
  BEFORE UPDATE ON cb_insured_person_bak
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
  nic   VARCHAR2(13);
  cnic  VARCHAR2(15);
BEGIN
  IF updating
     AND :NEW.old_nic_no IS NOT NULL THEN
    SELECT a.old_nic_no
    INTO   nic
    FROM   cb_insured_person_bak a
    WHERE  a.old_nic_no = :NEW.old_nic_no;
    
    IF SQL%FOUND THEN
      Raise_application_error(-20009,'This NIC '
                                     ||:NEW.old_nic_no
                                     ||' Already Exist');
    END IF;
  ELSIF updating
        AND :NEW.new_nic_no IS NOT NULL THEN
    SELECT a.new_nic_no
    INTO   cnic
    FROM   cb_insured_person_bak a
    WHERE  a.new_nic_no = :NEW.new_nic_no;
    
    IF SQL%FOUND THEN
      Raise_application_error(-20009,'This CNIC '
                                     ||:NEW.new_nic_no
                                     ||' Already Exist');
    END IF;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    NULL;
END; 

SQL> create table t (val integer);

Table created.

SQL> insert into t values(0);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> create index ti on t(val);

Index created.

SQL> alter table t add constraint t_unq unique (val) using index ti enable novalidate;

Table altered.

SQL> insert into t values(0);
insert into t values(0)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.T_UNQ) violated


SQL> insert into t values (1);

1 row created.

SQL> /
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.T_UNQ) violated

SQL> insert into t values(null);

1 row created.

SQL> alter table t add constraint t_nn check (val is not null) enable novalidate;

Table altered.

SQL> insert into t values(null);
insert into t values(null)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.T_NN) violated