Блог

У меня есть проблема, когда автономная транзакция, недоступная для меня, попытается вставить данные в таблицу, но данные могут уже существовать. Нам нужно найти решение для обработки ограничения уникальной целостности без его устранения, поскольку от проверки этого ограничения зависит другая программа.

Я попытался создать триггер «До», который удалит существующую запись, но это, похоже, не работает.

 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;
 

После этого я подумал об удалении ограничения и добавлении триггера с условным оператором, все записи из сломанной программы разные, поэтому я могу их различать. С помощью этого утверждения, если устройство уже существует, и вставка не происходит из сломанной программы, затем не удается продолжить, в противном случае удалите существующую запись и продолжите вставку.

Приведенное выше решение кажется рабочим решением, но мой вопрос в том, есть ли какая-либо функция, которая позволила бы мне сделать то же самое более элегантным способом?

Одним из вариантов было бы использовать instead of триггер. Однако это решение требует, чтобы вы переименовали свои таблицы и создали представления с теми именами, которые у них были. Таким образом, вы не влияете на логику приложения, однако это может повлиять на общую производительность, поэтому ее следует протестировать должным образом.

Тем не менее, использование триггеров для изменения неправильной логики приложения-не очень хорошая идея. Я понимаю ваше затруднительное положение, что иногда нам нужно находить обходные пути для решения существующих проблем, но это не делает его правильным.

В любом случае, ниже приведен простой пример, который вы можете применить к своей логике

 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.
 

Теперь мы создаем instead of триггер

 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.
 

С помощью этого триггера любая попытка нарушить ограничение сделает возможным обновление значения в конечной таблице.

 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