如何利用Oracle触发器（Trigger）高效记录数据库操作日志（Log）

最新推荐文章于 2024-06-17 01:30:46 发布

橙子or芒果

最新推荐文章于 2024-06-17 01:30:46 发布

阅读量705

点赞数 11

文章标签：数据库 oracle spring cloud mybatis

本文链接：https://blog.csdn.net/weixin_55661493/article/details/135359605

版权

作为一名“艺高人胆大”的资深程序员，面对自测或数据调整时，往往不甘于平凡，偏爱那条少有人走的捷径——直接与数据库“亲密接触”，跳过那些循规蹈矩的业务流程或接口。诚然，这种方式犹如游走在刀锋边缘，为数据库的数据纯度与测试结果的准确性平添了几分“惊喜”。在这场与数据的“非正式邂逅”中，常规日志似乎显得太过正经，难以捕捉到我们这些“特立独行者”的足迹。

于是乎，Oracle触发器闪亮登场，它就像那个机智的“守护神”，在你与数据库的每一次“秘密会谈”后，默默记录下一切，确保即使是最跳脱的数据库变动也能留下蛛丝马迹。有了这位“数据库侦探”，即便是最不按常理出牌的操作，也无法逃脱数据准确性的法眼，让测试中的每一个“即兴发挥”都变得有据可查，妙趣横生！这样一来，即便我们选择成为数据库的“直接对话者”，也能确保测试之旅既刺激又可靠，岂不乐哉？

好嘞，咱们书归正传，闲话少叙，直接给您奉上一段“笑傲江湖”的Oracle触发器代码，保证让您看了之后，既能感受到代码的严谨，又能会心一笑，仿佛是编程界的段子手悄悄留下的彩蛋！

首要步骤，匠心打造一个触发器专属的“日志宝藏库”表格，为数据变动留下印记。（此处可自行增加需存储的关键字段）

CREATE TABLE BACKUP_TABLE (
	SOUR_DATA_ID VARCHAR2 ( 128 ),
	SOUR_DATA_NO VARCHAR2 ( 4000 ),
	SOUR_DATA_NAME VARCHAR2 ( 4000 ),
	SOUR_DATA_STATUS VARCHAR2 ( 4000 ),
	SOUR_DATA_USER_ID VARCHAR2 ( 4000 ),
	SOUR_DATA_USER_NAME VARCHAR2 ( 4000 ),
	CREATED_BY VARCHAR2 ( 128 ),
	CREATION_DATE DATE,
	CREATED_IP VARCHAR2 ( 128 ),
	SOUR_DATA_TYPE VARCHAR2 ( 128 ) 
);
COMMENT ON TABLE BACKUP_TABLE IS '数据操作记录存储表';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_ID IS '数据主键';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_NO IS '数据编号';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_NAME IS '数据名称';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_STATUS IS '数据状态';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_USER_ID IS '数据创建人ID';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_USER_NAME IS '数据创建人名称';
COMMENT ON COLUMN BACKUP_TABLE.CREATED_BY IS '创建用户名';
COMMENT ON COLUMN BACKUP_TABLE.CREATION_DATE IS '创建时间';
COMMENT ON COLUMN BACKUP_TABLE.CREATED_IP IS '创建人IP';
COMMENT ON COLUMN BACKUP_TABLE.SOUR_DATA_TYPE IS '数据来源 删除^DELETE 修改^UPDATE';

触发器的局限性体现在两点：一是扩展性低，针对新增表需逐一配置，缺乏统一管理；二是执行频次高，每次数据库增删改操作均激活，难免对系统性能构成轻微压力。

(此处亮起警示灯，友情提示：触发器，乃是双刃剑一枚，用之需谨慎！)

以下便是那神奇一刻的守卫者——修改数据之时的“时光印刻”触发器。

---修改触发器 需监控表数据操作时增加 可能会影响部分性能---
CREATE OR REPLACE TRIGGER TABLE_UPDATE_TRIGGER
    BEFORE UPDATE  --在更新操作之前触发
    ON TABLE       --触发器作用在哪个表上
    FOR EACH ROW   --触发器作用在每一行上
DECLARE
    CREATED_BY VARCHAR2(128);
    CREATED_IP VARCHAR2(128);
BEGIN
    -- 获取会话用户名信息
    CREATED_BY := SYS_CONTEXT('USERENV', 'OS_USER');
    -- 获取会话用IP地址信息
    CREATED_IP := SYS_CONTEXT('USERENV', 'IP_ADDRESS');
    -- 将即将被修改的关键数据插入到 BACKUP_TABLE 中,并添加会话元数据
    INSERT INTO BACKUP_TABLE (SOUR_DATA_ID, SOUR_DATA_NO, SOUR_DATA_NAME, SOUR_DATA_STATUS, SOUR_DATA_USER_ID,
                              SOUR_DATA_USER_NAME, CREATED_BY, CREATION_DATE, CREATED_IP, SOUR_DATA_TYPE)
    VALUES (:OLD.FE_FILE_INFO_ID, :OLD.FILE_NO, :OLD.FILE_NAME, :OLD.FILE_STATUS,
            :OLD.UPLOAD_USER_ID, :OLD.UPLOAD_USER_NAME, CREATED_BY, SYSDATE, CREATED_IP, 'UPDATE');
END;
---------------------------------------字段讲解--------------------------------------
    --:OLD.TABLE_ID         为TABLE表中业务主键字段
    --:OLD.FILE_NO          为TABLE表中业务编号字段
    --:OLD.FILE_NAME        为TABLE表中业务名称字段
    --:OLD.FILE_STATUS      为TABLE表中业务状态字段
    --:OLD.UPLOAD_USER_ID   为TABLE表中业务创建人主键字段
    --:OLD.UPLOAD_USER_NAME 为TABLE表中业务创建人名称字段

    --以上为TABLE中各字段 可进行自定义添加或修改 :OLD. 此方法为获取在执行修改操作前的旧值
------------------------------------------------------------------------------------
    --CREATED_BY            为操作数数据库主机用户名字段
    --SYSDATE               为操作数据库时间字段
    --CREATED_IP            为操作数数据库主机IP地址字段
    --UPDATE                为操作类型

--以上为获取会话用户名信息、操作时间字、会话IP地址信息及操作类型

以下便是那神奇一刻的守卫者——删除数据之时的“时光印刻”触发器。

---删除触发器 需监控表数据操作时增加 可能会影响部分性能---
CREATE OR REPLACE TRIGGER TABLE_DELETE_TRIGGER
    BEFORE DELETE --在更新操作之前触发
    ON TABLE --触发器作用在哪个表上
    FOR EACH ROW --触发器作用在每一行上
DECLARE
    CREATED_BY VARCHAR2(128);
    CREATED_IP VARCHAR2(128);
BEGIN
    -- 获取会话用户名信息
    CREATED_BY := SYS_CONTEXT('USERENV', 'OS_USER');
    -- 获取会话用IP地址信息
    CREATED_IP := SYS_CONTEXT('USERENV', 'IP_ADDRESS');
    -- 将即将被删除的关键数据插入到 BACKUP_TABLE 中,并添加会话元数据
    INSERT INTO BACKUP_TABLE (SOUR_DATA_ID, SOUR_DATA_NO, SOUR_DATA_NAME, SOUR_DATA_STATUS, SOUR_DATA_USER_ID,
                              SOUR_DATA_USER_NAME, CREATED_BY, CREATION_DATE, CREATED_IP, SOUR_DATA_TYPE)
    VALUES (:OLD.FE_FILE_INFO_ID, :OLD.FILE_NO, :OLD.FILE_NAME, :OLD.FILE_STATUS,
            :OLD.UPLOAD_USER_ID, :OLD.UPLOAD_USER_NAME, CREATED_BY, SYSDATE, CREATED_IP, 'DELETE');
END;
---------------------------------------字段讲解--------------------------------------
--:OLD.TABLE_ID         为TABLE表中业务主键字段
--:OLD.FILE_NO          为TABLE表中业务编号字段
--:OLD.FILE_NAME        为TABLE表中业务名称字段
--:OLD.FILE_STATUS      为TABLE表中业务状态字段
--:OLD.UPLOAD_USER_ID   为TABLE表中业务创建人主键字段
--:OLD.UPLOAD_USER_NAME 为TABLE表中业务创建人名称字段

--以上为TABLE中各字段 可进行自定义添加或修改 :OLD. 此方法为获取在执行修改操作前的旧值
------------------------------------------------------------------------------------
--CREATED_BY            为操作数数据库主机用户名字段
--SYSDATE               为操作数据库时间字段
--CREATED_IP            为操作数数据库主机IP地址字段
--UPDATE                为操作类型

--以上为获取会话用户名信息、操作时间字、会话IP地址信息及操作类型

关于新增触发器那档子事儿，我原以为是可有可无的小配角，就没让它在剧本里露面。但若您各位看官觉得这小家伙其实藏着大能量，能让咱的数据舞台更加精彩纷呈，那请务必挥挥你们的魔法棒——哦不，动动手指留言告诉我。届时，我定当卷起袖子，为这篇科技小品添上浓墨重彩的一笔，让触发器的光辉照亮每个角落，保证诚意满满，趣味多多！