数据同步中一表操作的增删改反映到记录数据变化的表datainfo上的触发器写法

最新推荐文章于 2022-09-08 08:54:37 发布

lixinchaoo

最新推荐文章于 2022-09-08 08:54:37 发布

阅读量809

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： insert delete each oracle

本文链接：https://blog.youkuaiyun.com/lixinchaoo/article/details/4089999

数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍在MSSQL和Oracle中实现插入、删除及更新操作的触发器代码示例，通过这些触发器可以自动记录对特定表进行的数据操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、mssql

增加：

CREATE OR REPLACE TRIGGER pointInsertAffter ON point FOR INSERT AS declare @pid int begin select @pid= pid from inserted INSERT INTO datainfoo (src_tablename,key_src_id,createDate,operation,readtag,version) VALUES ( 'point',@pid,GETDATE(),'insert',0,1) end;

删除：

CREATE OR REPLACE TRIGGER pointdeleteAfter ON point FOR DELETE AS declare @pid int begin select @pid=pid from DELETED INSERT INTO datainfoo (src_tablename,key_src_id,createDate,operation,readtag,version) VALUES ( 'point',@pid,GETDATE(),'delete',0,1) end;

修改：

CREATE TRIGGER pointUpdateAfter ON point FOR UPDATE AS declare @pid int begin select @pid=pid from inserted INSERT INTO datainfoo (src_tablename,key_src_id,createDate,operation,readtag,version) VALUES ('point', @pid,GETDATE(),'update',0,1) end;

2、Oracle

增加：

DROP TRIGGER LXC.POINTTINSERTAFFTER;

CREATE OR REPLACE TRIGGER LXC.POINTTInsertAffter AFTER INSERT ON POINTT REFERENCING NEW AS NEWRecord OLD AS OLDRecord FOR EACH ROW
declare PID varchar2(50):=:NEWRecord.POINTID; BEGIN INSERT INTO DATAINFOO(src_tablename,key_src_id,createDate,operation,readtag,version) values('POINTT',PID,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'insert',0,1); end;
/

删除：

DROP TRIGGER LXC.POINTTDELETEAFTER;

CREATE OR REPLACE TRIGGER LXC.POINTTDELETEAFTER AFTER DELETE ON POINTT REFERENCING NEW AS NEWRecord OLD AS OLDRecord FOR EACH ROW
declare PID varchar2(50):=:OLDRecord.POINTID; BEGIN INSERT INTO DATAINFOO(src_tablename,key_src_id,createDate,operation,readtag,version) values('POINTT',PID,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'delete',0,1); end;
/

修改：

DROP TRIGGER LXC.POINTTUPDATEAFTER;

CREATE OR REPLACE TRIGGER LXC.POINTTUpdateAfter AFTER UPDATE ON POINTT REFERENCING NEW AS NEWRecord OLD AS OLDRecord FOR EACH ROW
declare PID varchar2(50):=:NEWRecord.POINTID; BEGIN INSERT INTO DATAINFOO(src_tablename,key_src_id,createDate,operation,readtag,version) values('POINTT',PID,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'update',0,1); end;
/