时间戳

首页 > 解决方案 > Oracle 触发器：条件插入或更新

oracle - Oracle 触发器：条件插入或更新

问题描述

我想创建一个触发器，它负责在将数据插入表x时将数据插入/更新到表中y。

表y具有该字段code，如果表中code已存在具有相同的记录，x则触发器应更新记录，否则应将新记录插入表中x。

我是 oracle 新手，知道如何为插入或更新创建触发器。但是，我不知道如何根据上述条件创建一个来插入和更新。

任何帮助表示赞赏。

编辑

为了更清楚地说明这个问题：

表y可以有多个相同的记录，code而表x只能有一个记录code（仅保留最新状态）。

因此，当向表中插入新记录时，y我应该决定是要在表中插入记录x还是只更新包含code.

标签： oracleplsqltriggers

解决方案

您可以使用复合触发器来整理所有插入和更新，然后发出一条MERGE语句（而不是MERGE为每个更改的行执行一条语句）。

例如，如果您的表Y可以包含多个日期的数据：

CREATE TABLE y (
  code INT,
  col1 INT,
  col2 INT,
  dt   DATE,
  PRIMARY KEY ( code, dt )
);

以及应包含最新日期数据的表X：

CREATE TABLE x (
  code INT PRIMARY KEY,
  col1 INT,
  col2 INT,
  dt   DATE
);

并且想要保持X更新（但不想将其创建为VIEWorMATERIALIZED VIEW并且不会进入 table 中UPDATE的DT列Y），那么您可以使用复合触发器：

CREATE TRIGGER log_y_changes_to_x
FOR INSERT OR UPDATE ON y
COMPOUND TRIGGER
  y_data y_table := y_table();
AFTER EACH ROW
  IS
  BEGIN
    y_data.EXTEND(1);
    y_data(y_data.COUNT) := y_object( :NEW.code, :NEW.col1, :NEW.col2, :NEW.dt );
  END AFTER EACH ROW;
AFTER STATEMENT
  IS
  BEGIN
    MERGE INTO x
    USING (
      SELECT code,
             col1,
             col2,
             dt
      FROM   (
        SELECT t.*,
               ROW_NUMBER() OVER ( PARTITION BY code ORDER BY dt DESC ) rn
        FROM   TABLE(y_data) t
      )
      WHERE rn = 1
    ) y
    ON ( y.code = x.code )
    WHEN MATCHED THEN
      UPDATE
      SET   col1 = y.col1,
            col2 = y.col2,
            dt   = y.dt
      WHERE y.dt >= x.dt
    WHEN NOT MATCHED THEN
      INSERT ( code, col1, col2, dt )
      VALUES ( y.code, y.col1, y.col2, y.dt );
  END AFTER STATEMENT;
END;
/

并有以下类型：

CREATE TYPE y_object IS OBJECT (
  code INT,
  col1 INT,
  col2 INT,
  dt   DATE
);

CREATE TYPE y_table IS TABLE OF y_object;

然后插入数据：

INSERT INTO y ( code, col1, col2, dt )
SELECT 1, 0, 2, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 1, 0, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 0, 3, DATE '2020-01-02' FROM DUAL UNION ALL
SELECT 3, 3, 3, DATE '2020-01-01' FROM DUAL;

然后表X包含：

代码 | COL1 | COL2 | DT                 
---: | ---: | ---: | :-----------------
   2 | 0 | 3 | 2020-01-02 00:00:00
   3 | 3 | 3 | 2020-01-01 00:00:00
   1 | 0 | 2 | 2020-01-01 00:00:00

而且，如果您更新数据：

UPDATE y
SET    col1 = col1 + 3
WHERE  code <= 2
AND    dt = DATE '2020-01-01';

然后表X包含*（注意：代码2行未更新，因为它不是最新的行）：

代码 | COL1 | COL2 | DT                 
---: | ---: | ---: | :-----------------
   2 | 0 | 3 | 2020-01-02 00:00:00
   3 | 3 | 3 | 2020-01-01 00:00:00
   1 | 3 | 2 | 2020-01-01 00:00:00

和：

INSERT INTO y ( code, col1, col2, dt )
SELECT 3, 5, 5, DATE '2020-01-05' FROM DUAL;

然后X包含

代码 | COL1 | COL2 | DT                 
---: | ---: | ---: | :-----------------
   2 | 0 | 3 | 2020-01-02 00:00:00
   3 | 5 | 5 | 2020-01-05 00:00:00
   1 | 3 | 2 | 2020-01-01 00:00:00

和：

INSERT INTO y ( code, col1, col2, dt )
SELECT 3, 4, 4, DATE '2020-01-04' FROM DUAL;

ThenX不变，因为该行比前一个旧。

db<>在这里摆弄

推荐阅读