我花了几个小时来为这个问题找到一个合适的解决方案,所以我在找到解决方案后以问答的方式创建了这个问题。
我有一张这样的表:
CREATE TABLE SoftwareVersion
(
ID NUMBER NOT NULL,
DeviceID NUMBER NOT NULL,
ReadoutDate DATE NOT NULL,
Version VARCHAR2(20 CHAR) NOT NULL,
NextReadoutDate DATE NULL
);
此表包含设备的软件版本代码。每个设备可以有一个或多个软件版本。执行此表的 INSERT 语句的导入过程仅填充 ID、DeviceID、ReadoutDate 和 Version。ReadoutDate 是导入过程的当前时间戳。因此,软件版本的第一次出现被假定为“软件版本自读出日期起有效”。
我的问题是现在我需要软件版本的范围。每个软件版本从哪个时间戳到哪个时间戳有效?
为了提高性能(并且因为我已经获得了该表的触发器),我添加了由触发器维护的 NextReadoutDate 列。它将接收该 DeviceID 的下一个有效 ReadoutDate 值。所以每个软件版本都会变成一个范围(有效从...到)。
为了避免变异表问题 (ORA-04091),我收集了我正在使用 AFTER ROW 触发器的语句的所有更新信息,如下所示:
CREATE OR REPLACE TRIGGER TRG_SoftwareVersion1
AFTER INSERT OR UPDATE OR DELETE ON SoftwareVersion
FOR EACH ROW
BEGIN
IF UPDATING THEN
IF :OLD.DeviceID = :NEW.DeviceID AND :OLD.ReadoutDate = :NEW.ReadoutDate OR
(:OLD.DeviceID IS NULL OR :OLD.ReadoutDate IS NULL) AND
(:NEW.DeviceID IS NULL OR :NEW.ReadoutDate IS NULL) THEN
-- Nothing to do
RETURN;
END IF;
END IF;
-- Evaluate later
INSERT INTO
SoftwareVersion_TrgHelper
(
OldDeviceID,
OldReadoutDate,
NewDeviceID,
NewReadoutDate
)
VALUES
(
:OLD.DeviceID,
:OLD.ReadoutDate,
:NEW.DeviceID,
:NEW.ReadoutDate
);
END;
之后,我在 AFTER STATEMENT 触发器中更新表,如下所示:
CREATE OR REPLACE TRIGGER TRG_SoftwareVersion2
AFTER INSERT OR UPDATE OR DELETE ON SoftwareVersion
DECLARE
CURSOR cCursorMain IS SELECT * FROM SoftwareVersion_TrgHelper FOR UPDATE;
vOldDeviceID NUMBER;
vOldReadoutDate DATE;
vNewDeviceID NUMBER;
vNewReadoutDate DATE;
BEGIN
OPEN cCursorMain;
LOOP
FETCH cCursorMain INTO vOldDeviceID, vOldReadoutDate, vNewDeviceID, vNewReadoutDate;
EXIT WHEN cCursorMain%NOTFOUND;
IF UPDATING OR DELETING THEN
UPDATE
SoftwareVersion SV
SET
SV.NextReadoutDate = (SELECT MIN(SV2.ReadoutDate) KEEP (DENSE_RANK FIRST ORDER BY SV2.ReadoutDate ASC, SV2.ID ASC) FROM SoftwareVersion SV2 WHERE SV.DeviceID = SV2.DeviceID AND SV.ReadoutDate < SV2.ReadoutDate)
WHERE
SV.DeviceID = vOldDeviceID AND
SV.ReadoutDate <= vOldReadoutDate;
END IF;
IF UPDATING OR INSERTING THEN
UPDATE
SoftwareVersion SV
SET
SV.NextReadoutDate = (SELECT MIN(SV2.ReadoutDate) KEEP (DENSE_RANK FIRST ORDER BY SV2.ReadoutDate ASC, SV2.ID ASC) FROM SoftwareVersion SV2 WHERE SV.DeviceID = SV2.DeviceID AND SV.ReadoutDate < SV2.ReadoutDate)
WHERE
SV.DeviceID = vNewDeviceID AND
SV.ReadoutDate <= vNewReadoutDate;
END IF;
DELETE FROM SoftwareVersion_TrgHelper WHERE CURRENT OF cCursorMain;
END LOOP;
CLOSE cCursorMain;
END;
/
不幸的是,当我再次执行导入过程时,我得到了一个 ORA-00036。我需要一种方法来避免触发递归。
Oracle 没有提供 TRIGGER_NESTLEVEL 之类的函数(就像 SQL 服务器那样),而且似乎根本没有多少函数可以避免递归(您可以使用会话变量或类似的东西,但这可能会导致其他错误)。
我的简单解决方案是让我的触发器仅在特定列被修改时触发。这是可能的,因为触发器中的 UPDATE 语句仅修改 NextReadoutDate 列。
代替:
AFTER INSERT OR UPDATE OR DELETE ON SoftwareVersion
我用:
AFTER INSERT OR UPDATE OR DELETE OF DeviceID, ReadoutDate ON SoftwareVersion
然后一切都很好。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句