Реализовать SCD типа II в триггере Oracle с использованием слияния

На всю жизнь я не могу заставить следующий запрос работать ... по сути, это модификация следующего билета: Триггер с оператором Merge в oracle.

CREATE OR REPLACE TRIGGER TABLE_UPDATE
  AFTER INSERT OR UPDATE ON DIM_TABLE
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF INSERTING OR UPDATING
THEN
    MERGE INTO DIM_TABLE T_1
          USING( SELECT COL_1, max(VALID_FROM) AS LAST_DATE FROM 
DIM_TABLE
          GROUP BY COL_1) T_2
          ON (T_1. COL_1= T_2. COL_1)

    WHEN NOT MATCHED THEN INSERT (T_1. VALID_FROM) VALUES(SYSDATE)

    WHEN MATCHED THEN
      UPDATE
      SET T_1.VALID_UNTIL = T_2.LAST_DATE
      WHERE T_1. VALID_UNTIL is null 
      AND T_1. VALID_FROM <> T_2.LAST_DATE;
   COMMIT;
 END IF; 
END;

Желаемая функциональность: значение вводится в базу данных (COL_1). Если столбец не соответствует, то дата VALID_FROM вводится как sysdate. Если есть совпадение, скрипт должен обновить значение VALID_UNTIL для предыдущей строки (которая до сих пор была нулевой, потому что оставалась действительной).

Постоянно получаю следующие ошибки: превышен тупик и максимальный уровень рекурсий (50)


oracle triggers plsql scd database-trigger
person Danny    schedule 29.04.2019    source источник
comment
Вы пытаетесь выполнить слияние с той же таблицей, против которой выступает триггер - это может вызвать ошибку изменяющейся таблицы, но в этом случае, похоже, это обходится - я думаю, с помощью автономного взлома - и вместо этого рекурсивно обновляется (слияние вызывает ошибку insert / update, который запускает триггер, который вызывает вставку / обновление, которое ...). «Билет», на который вы ссылаетесь, включает в себя разные столы. Я бы вернулся к вашим реальным бизнес-требованиям и подхожу к ним по-другому.   -  person Alex Poole    schedule 29.04.2019

Ответы (2)


arrow_upward
0
arrow_downward

Мне действительно трудно получить ваше требование. Мне кажется, что вы хотите, чтобы VALID_FROM было либо sysdate, либо максимальным значением для каждого COL_1.

Я бы сделал это, используя :NEW для управления текущей строкой

set linesize 250
drop table dim_Table;
create table dim_table (col_1 varchar2(50), valid_from timestamp);

CREATE OR REPLACE TRIGGER TABLE_UPDATE
  before INSERT OR UPDATE ON DIM_TABLE
FOR EACH ROW
DECLARE
  l_valid_from date;
BEGIN
  select max(valid_from) 
    into l_valid_from
   from  dim_table t1
   where t1.col_1 = :new.col_1;
   if l_valid_from is null then
     :new.valid_from := sysdate;
   else
     :new.valid_from := l_valid_from;
   end if;
END;

insert into dim_table (col_1) values ('TEST1');
select * from dim_table;
exec dbms_lock.sleep(1);
insert into dim_table (col_1) values ('TEST1');
select * from dim_table;
exec dbms_lock.sleep(1);
insert into dim_table (col_1) values ('TEST2');
select * from dim_table;
exec dbms_lock.sleep(1);
insert into dim_table (col_1) values ('TEST1');
select * from dim_table;
exec dbms_lock.sleep(1);
insert into dim_table (col_1) values ('TEST2');
select * from dim_table;

Выход:

Table dropped.
Table created.
Trigger created.
1 row created.

COL_1                                              VALID_FROM                                        
-------------------------------------------------- --------------------------------------------------
TEST1                                              30/04/2019 09:13:10.000000                        
1 row selected.
 PL/SQL procedure successfully completed.
1 row created.

COL_1                                              VALID_FROM                                        
-------------------------------------------------- --------------------------------------------------
TEST1                                              30/04/2019 09:13:10.000000                        
TEST1                                              30/04/2019 09:13:10.000000                        

2 rows selected.
 PL/SQL procedure successfully completed.
1 row created.

COL_1                                              VALID_FROM                                        
-------------------------------------------------- --------------------------------------------------
TEST1                                              30/04/2019 09:13:10.000000                        
TEST1                                              30/04/2019 09:13:10.000000                        
TEST2                                              30/04/2019 09:13:12.000000                        

3 rows selected.
 PL/SQL procedure successfully completed.
1 row created.

COL_1                                              VALID_FROM                                        
-------------------------------------------------- --------------------------------------------------
TEST1                                              30/04/2019 09:13:10.000000                        
TEST1                                              30/04/2019 09:13:10.000000                        
TEST2                                              30/04/2019 09:13:12.000000                        
TEST1                                              30/04/2019 09:13:10.000000                        

4 rows selected.
 PL/SQL procedure successfully completed.
1 row created.

COL_1                                              VALID_FROM                                        
-------------------------------------------------- --------------------------------------------------
TEST1                                              30/04/2019 09:13:10.000000                        
TEST1                                              30/04/2019 09:13:10.000000                        
TEST2                                              30/04/2019 09:13:12.000000                        
TEST1                                              30/04/2019 09:13:10.000000                        
TEST2                                              30/04/2019 09:13:12.000000                        

5 rows selected.
person hol    schedule 30.04.2019

arrow_upward
0
arrow_downward

спасибо @hol, @Alex Poole, кажется, я его взломал. Я использую триггер для вставки данных в VALID_FROM каждый раз, когда что-то добавляется:

 CREATE TABLE "DIM_TEST" (  "COL_1" VARCHAR2(20 BYTE), "VALID_UNTIL" TIMESTAMP (6), 
"DIM_TEST_PK" NUMBER, "VALID_FROM" TIMESTAMP (6) DEFAULT NULL) 

  create or replace TRIGGER T_DIM_TEST_DATE_INSERT
  BEFORE INSERT ON DIM_TEST
  FOR EACH ROW
  BEGIN
    :new.VALID_FROM := sysdate;
  END;

а для части, которая обновляет предыдущую строку VALID_FROM при добавлении дубликата, у меня есть следующий триггер:

 create or replace TRIGGER TABLE_UPDATE
 after INSERT or update
 ON DIM_TEST
 FOR EACH ROW
 DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
 IF INSERTING
 THEN
    MERGE INTO DIM_TEST T_1
    USING (select :new.COL_1 from DUAL) T_2
      ON (T_1.COL_1 = :new.COL_1)

WHEN MATCHED THEN
  UPDATE
  SET T_1.VALID_UNTIL = (SYSDATE)
  WHERE T_1. VALID_UNTIL is null;
  --AND T_1. VALID_FROM <> (SELECT max(VALID_FROM) FROM DIM_TEST);
 END IF;  
COMMIT;
END;

Единственным недостатком является то, что если существующая строка обновляется, чтобы быть дубликатом, то второй триггер не сработает из-за предложения IF INSERTING ... когда я меняю его на IF INSERTING или UPDATING, он генерирует бесконечный цикл ... но я могу ограничить пользователей только вставками, так что это не должно быть проблемой.

person Danny    schedule 02.05.2019