本教程详细介绍了如何在oracle数据库中利用数据库触发器，实现对指定列（如`dat_update`）在数据插入或更新时自动填充或更新为数据库系统时间（`sysdate`）。通过创建`before insert or update`触发器，确保了时间戳的精确性和一致性，无需应用层额外干预，提供了一个高效且可靠的数据库级解决方案。
在现代数据库应用中，自动记录数据的创建时间或最后更新时间是一项常见且重要的需求。这有助于追踪数据生命周期、审计操作以及进行版本控制。对于Oracle数据库而言，实现这一功能最直接且高效的方法是利用数据库触发器，它能够在数据发生变化时自动执行预定义的操作，从而确保时间戳的准确性和一致性。
1. 理解需求与挑战
我们的目标是让一个特定的列（例如 dat_update）在每次数据插入或更新时，自动获取并存储Oracle数据库当前的系统日期和时间（SYSDATE）。虽然一些ORM框架（如Hibernate）提供了像@ColumnTransformer这样的注解，尝试在应用层层面进行数据转换，但在某些情况下，尤其是在需要直接获取数据库系统时间且不依赖应用层传入值时，这些方法可能无法如预期般工作。此时，将逻辑下沉到数据库层，利用其原生能力，是更稳健的选择。
2. 核心解决方案：Oracle数据库触发器
Oracle数据库触发器是一种特殊的存储过程，它在表上发生特定事件（如INSERT、UPDATE、DELETE）时自动执行。对于自动更新时间戳的需求，BEFORE INSERT OR UPDATE 类型的行级触发器是理想选择。
2.1 创建表结构
首先，我们需要一个包含日期时间列的表。这里我们创建一个名为 test 的表，其中包含一个 dat_update 列，用于存储更新时间。
CREATE TABLE test
(
id NUMBER GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(10),
dat_update DATE
);- id: 使用 GENERATED ALWAYS AS IDENTITY 定义一个自增主键。
- name: 一个简单的VARCHAR2类型列。
- dat_update: 存储日期和时间的列，使用 DATE 类型。Oracle的DATE类型可以存储日期和时间信息。
2.2 定义自动更新触发器
接下来，我们创建触发器 trg_biu_test。这个触发器会在每次对 test 表进行插入或更新操作之前，为当前行的 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 操作之前触发。
- FOR EACH ROW: 这是一个行级触发器，意味着它将为每个受影响的行执行一次。
- BEGIN ... END;: 触发器的主体，包含要执行的SQL语句。
- :new.dat_update := SYSDATE;: 这是核心逻辑。在行级触发器中，:new 是一个伪记录，代表即将插入或更新的行的数据。我们将 :new.dat_update 设置为当前的数据库系统日期和时间 (SYSDATE)。
3. 验证与测试
为了验证触发器是否按预期工作，我们可以执行一些插入和更新操作，并检查 dat_update 列的值。
为了更好地观察日期和时间，可以先设置会话的日期格式：
ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss';
3.1 插入数据测试
插入一条新记录到 test 表中，不指定 dat_update 列的值。触发器会自动填充它。
INSERT INTO test (name) VALUES ('Little');查询数据，可以看到 dat_update 已经被填充：
新视窗企业管理系统是一款小巧、实用、利于后续开发的ASP程序。适合大中小型企业的网站建设。1、新闻管理 2、产品管理 3、订单管理 4、广告管理 5、下载管理 6、留言管理 8、单页栏目(如企业简介,资质荣誉)9、人才招聘等等。 新视窗企业管理系统 5.1 更新日志:1、修改产品列表的图片自动缩略，防止图片变形.2、修改后台添加产品分类时，排序ID不写入数据库的错误.3、修改首页企业简介的链接地址
SELECT * FROM test ORDER BY id;
输出示例：
ID NAME DAT_UPDATE
---------- ---------- -------------------
1 Little 01.12.2022 20:22:03再次插入一条记录：
INSERT INTO test (name) VALUES ('Foot');3.2 更新数据测试
更新现有记录。触发器应该更新 dat_update 列的时间戳。
UPDATE test SET name = 'Yasuda' WHERE name = 'Little';
再次查询所有数据：
SELECT * FROM test ORDER BY id;
输出示例（注意第一条记录的 DAT_UPDATE 已经更新）：
ID NAME DAT_UPDATE
---------- ---------- -------------------
1 Yasuda 01.12.2022 20:22:33
2 Foot 01.12.2022 20:22:19从结果可以看出，当 name 从 'Little' 更新为 'Yasuda' 时，dat_update 列的时间戳也随之更新，而未被修改的 'Foot' 记录的时间戳保持不变。这证明触发器工作正常。
4. 注意事项与最佳实践
- 时区管理： SYSDATE 返回的是数据库服务器操作系统的当前日期和时间。如果需要处理不同时区或UTC时间，可以考虑使用 SYSTIMESTAMP（返回带有时区的时间戳）或 CURRENT_TIMESTAMP（返回会话时区的时间戳）。根据具体的业务需求选择合适的函数。
- 性能影响： 触发器会在每次DML操作时执行，这会带来轻微的性能开销。然而，对于简单的赋值操作，这种开销通常可以忽略不计，并且在保证数据一致性方面带来的收益远大于其成本。
- ORM集成： 当使用Hibernate、JPA等ORM框架时，应用层无需为 dat_update 列显式赋值。ORM在执行 INSERT 或 UPDATE 操作后，如果需要获取最新的时间戳，只需重新加载实体或刷新会话即可。
- 替代方案： 虽然数据库触发器是推荐的解决方案，但也可以在应用层通过ORM的生命周期回调（如JPA的@PrePersist和@PreUpdate）来设置时间戳。然而，这种方式的缺点是依赖于应用层的逻辑，如果数据通过其他方式（如SQL脚本）直接修改，时间戳可能不会被更新。数据库触发器则提供了更强的保障。
- 可读性与维护： 触发器代码应简洁明了，并附有必要的注释。过多的复杂逻辑不建议放在触发器中，以免影响数据库性能和维护性。
总结
通过在Oracle数据库中创建 BEFORE INSERT OR UPDATE FOR EACH ROW 触发器，我们可以高效且可靠地实现对特定列（如 dat_update）的自动时间戳更新。这种数据库级别的解决方案确保了数据的一致性和准确性，减少了应用层的复杂性，是管理数据修改时间的推荐方法。理解并恰当运用数据库触发器，能够显著提升数据库应用的健壮性和可维护性。
