时间戳

首页 > 解决方案 > 使用合并在 Oracle 触发器中实现类型 II SCD

oracle - 使用合并在 Oracle 触发器中实现类型 II SCD

问题描述

对于我的生活，我无法让以下查询工作......基本上它是对以下票证的修改： 在 oracle 中使用 Merge 语句触发。

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）

标签： oracleplsqltriggersdatabase-triggerscd

解决方案

I really struggle getting your requirement. To me it seems that you want VALID_FROM to be either sysdate or the max value for each COL_1.

That I would do by making use of :NEW to manipulate current row

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;

Output:

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.

推荐阅读