Oracle两表同步的触发器写法

最新推荐文章于 2025-07-21 14:19:51 发布

原创

最新推荐文章于 2025-07-21 14:19:51 发布 · 1.4k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#DataBase（数据仓库）

该博客介绍了如何使用Oracle数据库的触发器实现两个表之间的数据同步，包括备份、操作日志记录以及针对插入、删除、更新操作的具体处理。通过创建触发器Tri_BDC_XMNDZFW和Tri_BDC_XMNDZFW_MX，实现实时的数据变更同步到备份表，并记录变更日志。

select * from BDC_XMNDZFW;

select * from BDC_XMNDZFW_MX;

----复制备份表（若果想要备份的功能，可令条件为1=1
Create Table BDC_XMNDZFW_Backup As select * from BDC_XMNDZFW Where 1=2;
Create Table BDC_XMNDZFW_MX_Backup As select * from BDC_XMNDZFW_MX Where 1=2;

--创建操作日志表
Drop Table ChgLogTab;

create Table ChgLogTab
(
       TableName Varchar2(32),
       ForeignKey Varchar2(50),
       MainKey Varchar2(36),
       DMLType Varchar2(8),
       DMLTime DATE,
       PersonName VARCHAR2(30),
       ProgramExe VARCHAR2(64),
       HostMachine Varchar2(64),
       IpAddr CHAR(15)
);


Select * from ChgLogTab;
Delete From ChgLogTab;
------------------------------------------------------------------
---不授权给用户，触发器就没法看到这张表
sys as sysdba;
grant select on V_$SESSION to BDCDJ_ZF;
------------------------------------------------------------------

--------无效的主机名绑定变量：解决方案，Values的参数列表中缺少一个分隔符（，）
Create or Replace Trigger Tri_BDC_XMNDZFW
Before insert or delete or update on BDC_XMNDZFW for each row
Declare
TabName Varchar2(32);
ForeignKey Varchar2(50);
MainKey Varchar2(36);
DMLType Varchar2(8);
PersonName VARCHAR2(30);
ProgramExe VARCHAR2(64);
HostMachine VARCHAR2(64);
IpAddr CHAR(15);
DMLTime Date;
strTable Varchar(32);
strSql Varchar(1024);
Begin
      TabName := 'BDC_XMNDZFW';
      strTable := TabName||'_Backup';
      ForeignKey := null;
      MainKey := null;
      select username into PersonName from v$session where audsid = userenv('sessionid');
      select program into ProgramExe from v$session where audsid = userenv('sessionid');
      select machine into HostMachine from v$session where audsid = userenv('sessionid');
      select sys_context('userenv','ip_address') into IpAddr from v$session where audsid = userenv('sessionid');
      select sysdate into DMLTime from dual;
      --select username in