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

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

lixinchaoo

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

阅读量768

点赞数

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

本文链接：https://blog.csdn.net/lixinchaoo/article/details/4089999

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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;
/