本文详细介绍了在oracle数据库中，如何通过创建数据库触发器，实现对指定日期时间列（如`dat_update`）的自动管理。当数据行发生插入或更新操作时，该列将自动接收数据库当前的`sysdate`值，确保时间戳的准确性和一致性。文章通过具体示例代码演示了触发器的创建、测试及其工作原理，并探讨了相较于orm层解决方案的优势。
在数据库应用开发中，一个常见的需求是自动记录数据行的创建时间或最后更新时间。特别是在Oracle数据库环境中，我们希望某个日期时间列（例如dat_update）能够在每次数据插入或更新时，自动填充数据库的系统日期和时间（SYSDATE）。虽然一些ORM框架（如Hibernate）提供了@ColumnTransformer等注解来尝试在应用层处理此类逻辑，但对于确保数据层面的准确性、一致性和避免时区问题，数据库层面的解决方案往往更为健壮和推荐。
采用数据库触发器实现自动时间戳更新
在Oracle数据库中，实现自动更新SYSDATE到指定列的最有效和推荐方法是使用数据库触发器（Database Trigger）。触发器是绑定到特定表上的PL/SQL块，当该表上发生预定义的DML操作（如INSERT、UPDATE、DELETE）时，触发器会自动执行。
1. 创建示例表
首先，我们创建一个示例表test，其中包含一个用于存储自动更新时间的dat_update列。
CREATE TABLE test (
id NUMBER GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(10),
dat_update DATE
);这里，id列被定义为自增主键，name是普通字符串列，dat_update是DATE类型，用于存储系统日期。
2. 创建数据库触发器
接下来，我们创建触发器trg_biu_test，它将在test表发生INSERT或UPDATE操作之前触发，并为dat_update列设置SYSDATE。
CREATE OR REPLACE TRIGGER trg_biu_test BEFORE INSERT OR UPDATE ON test FOR EACH ROW BEGIN :new.dat_update := SYSDATE; END; /
触发器解析：
- CREATE OR REPLACE TRIGGER trg_biu_test: 定义或替换名为trg_biu_test的触发器。
- BEFORE INSERT OR UPDATE ON test: 指定触发器在test表执行INSERT或UPDATE操作之前触发。使用BEFORE确保在数据写入数据库前，dat_update列就已经被赋值。
- FOR EACH ROW: 表示这是一个行级触发器，即每次操作影响一行数据时，触发器都会执行一次。
- BEGIN ... END;: 触发器的PL/SQL块。
- :new.dat_update := SYSDATE;: 这是核心逻辑。在行级触发器中，:new伪记录变量引用了即将被插入或更新的行的新值。因此，:new.dat_update := SYSDATE;会将当前的数据库系统日期和时间赋值给该行的dat_update列。
3. 测试触发器功能
为了验证触发器是否按预期工作，我们可以执行一些插入和更新操作，并查询结果。在测试之前，为了方便观察日期格式，可以临时设置会话的日期格式：
ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss';
插入数据：
INSERT INTO test (name) VALUES ('Little');
INSERT INTO test (name) VALUES ('Foot');
SELECT * FROM test ORDER BY id;执行上述插入语句后，查询结果将显示dat_update列已自动填充为插入时的数据库系统时间：
ID NAME DAT_UPDATE
---------- ---------- -------------------
1 Little 01.12.2022 20:22:03
2 Foot 01.12.2022 20:22:19更新数据：
现在，我们尝试更新一行数据：
UPDATE test SET name = 'Yasuda' WHERE name = 'Little'; SELECT * FROM test ORDER BY id;
查询结果会显示，id为1的行的dat_update列已经更新为当前操作的系统时间，而id为2的行保持不变：
ID NAME DAT_UPDATE
---------- ---------- -------------------
1 Yasuda 01.12.2022 20:22:33
2 Foot 01.12.2022 20:22:19这证明了触发器在INSERT和UPDATE操作时都成功地自动更新了dat_update列。
注意事项与最佳实践
- 数据库层面的控制： 使用数据库触发器是确保时间戳一致性和准确性的最可靠方式，因为它直接在数据写入数据库之前生效，不受应用程序逻辑、时区设置或网络延迟等因素的影响。
- 性能考量： 对于极高并发的写入操作，触发器可能会引入轻微的性能开销，但对于大多数业务场景，这种开销通常可以忽略不计。
-
创建时间与更新时间： 如果需要同时记录创建时间和最后更新时间，可以创建两个DATE或TIMESTAMP类型的列（例如created_at和updated_at），并在触发器中分别处理：
- BEFORE INSERT: created_at := SYSDATE; updated_at := SYSDATE;
- BEFORE UPDATE: updated_at := SYSDATE;
- 或者更简洁地，在BEFORE INSERT OR UPDATE触发器中：
IF INSERTING THEN :new.created_at := SYSDATE; END IF; :new.updated_at := SYSDATE;
-
数据类型选择：
- DATE类型：存储日期和时间，精度到秒。
- TIMESTAMP类型：比DATE更精确，可以存储到小数秒。如果需要更高的精度，推荐使用TIMESTAMP。
- TIMESTAMP WITH LOCAL TIME ZONE：如果需要自动处理时区转换，可以将时间戳存储为TIMESTAMP WITH LOCAL TIME ZONE，它会根据会话时区自动转换。
- ORM框架集成： 当使用ORM框架时，通常会将这些自动生成的列标记为只读或忽略，以避免ORM尝试写入它们，从而完全依赖数据库触发器来管理这些值。
总结
通过在Oracle数据库中创建BEFORE INSERT OR UPDATE FOR EACH ROW触发器，我们可以高效且可靠地实现对指定日期时间列的自动更新，使其在每次数据插入或修改时自动记录数据库的SYSDATE。这种方法保证了时间戳的准确性、数据一致性，并简化了应用程序的逻辑，是管理数据库时间戳列的专业且推荐的实践。
