How to get around a unique constraint violation?-CodePudding

I have an issue where a program out of my reach will try to insert data into a table, but the data may already be existing. We need to find a solution to handle the unique integrity constraint without removing the constraint as another program depends on this constraint validation.

I've tried creating a before trigger that will delete the existing entry, but that doesn't seem to work.

CREATE OR REPLACE TRIGGER TG_BEFORE_INSERT_REPLACE_DC_DEVICE_TACTICAL_FIX_INV_LOADER
BEFORE INSERT
ON DC_DEVICE
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    FAKE_DEVICE_STATUS VARCHAR2(3 BYTE);
BEGIN
    -- constraint exception occurs before this statement
    DELETE FROM DC_DEVICE WHERE DEVICE_ID_PK = :NEW.DEVICE_ID_PK;
END;

After that, I've thought of deleting the constraint and adding a trigger with a conditional statement, all entries from the broken program are different, so I can differentiate them. With that statement, if the device already exists and the insert does not come from the broken program, then fail to proceed, else delete the existing entry and proceed with the insertion.

The solution above seems like a working solution, but my question is, is there any feature that would let me do the same in a more elegant way?

CodePudding user response：

One option would be to use instead of trigger. However, this solution requires for you to rename your tables and create views with the names they had. That way you don't affect the application logic, however it could affect the overall performance, so it should be tested properly.

Nevertheless, using triggers to change wrong application logic is not a very good idea. I understand your predicament that sometimes we need to find workarounds to existing problems, but that does not make it right.

Anyway, below a simple example that you can apply to your logic

SQL> create table t ( c1 number primary key , c2 varchar2(1) ) ;

Table created.

SQL> alter table t rename to tbl_t ;

Table altered.

SQL>  create view t as ( select c1 , c2 from tbl_t ) ;

View created.

Now we create a instead of trigger

SQL> create or replace trigger tr_v_t
  2  instead of insert
  3  on t
  4  for each row
  5  declare
  6    pk_violation_exception exception;
  7    pragma exception_init(pk_violation_exception, -00001);
  8  begin
  9    insert into tbl_t (c1,c2)
 10    values ( :new.c1,:new.c2 );
 11    exception
 12      when pk_violation_exception then
 13        dbms_output.put_line('ora-00001 (pk_violation_exception) captured');
 14        update tbl_t
 15        set c2   = :new.c2
 16        where c1 = :new.c1 ;
 17* end;
SQL> /

Trigger created.

With this trigger, any attempt to violate the constraint will make the update of the value possible in the final table.

SQL> select * from t ;

no rows selected

SQL> insert into t values ( 1 , 'A' ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> insert into t values ( 2, 'B' ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> insert into t values ( 2, 'C' ) ;
ORA-00001 (pk_violation_exception) captured

1 row created.

SQL> select * from tbl_t ;

        C1 C
---------- -
         1 A
         2 C