pragma autonomous_transaction 自治事务实例

最新推荐文章于 2021-09-16 13:23:37 发布

转载最新推荐文章于 2021-09-16 13:23:37 发布 · 315 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/su1643/p/6567472.html

文章标签：

#java

本文介绍了一个Oracle数据库触发器的创建示例，该触发器用于在进行插入、更新或删除操作时，同步数据到远程数据库。通过使用自主事务声明，确保了数据的一致性和完整性。

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

要点：

1、声明

declare pragma autonomous_transaction;

2、结尾做事务提交commit。

commit;

create or replace trigger TRI_USRP_DET_SYNC after insert or update or delete on usrp_det
for each row
declare pragma autonomous_transaction;
begin
   if Inserting then
     insert into huinan.usrp_det@huinanerp
     (USRP_USER, USRP_PROG, USRP_RUN, USRP_INSERT, USRP_MODIFY, USRP_DELETE, USRP_PRINT, USRP_EXPORT, USRP_PST, USRP_UNPST, USRP_CHK,
     USRP_UNCHK, USRP_WF_SUBMIT, USRP_WF_CANCEL, USRP_VIEW_COST, USRP_VIEW_OTHERS, USRP_MOD_OTHERS, USRP_DEL_OTHERS, USRP_DESIGN, USRP_NEGATIVE,
     USRP_PERM1, USRP_PERM2, USRP_PERM3, USRP_PERM4, USRP_PERM5, USRP_PERM6, USRP_CHAR1, USRP_CHAR2, USRP_CHAR3, USRP_CHAR4, USRP_CHAR5, USRP_CHAR6,
     USRP_QTY1, USRP_QTY2, USRP_CRT_BY, USRP_CRT_DATE, USRP_MOD_TIMES, USRP_MOD_BY, USRP_MOD_DATE, USRP_UPLOAD, USRP_VIEW, USRP_DOWNLOAD, USRP_VIEW_HIST)
     values
     (:new.USRP_USER, :new.USRP_PROG, :new.USRP_RUN, :new.USRP_INSERT, :new.USRP_MODIFY, :new.USRP_DELETE, :new.USRP_PRINT, :new.USRP_EXPORT, :new.USRP_PST, :new.USRP_UNPST, :new.USRP_CHK,
     :new.USRP_UNCHK, :new.USRP_WF_SUBMIT, :new.USRP_WF_CANCEL, :new.USRP_VIEW_COST, :new.USRP_VIEW_OTHERS, :new.USRP_MOD_OTHERS, :new.USRP_DEL_OTHERS, :new.USRP_DESIGN, :new.USRP_NEGATIVE,
     :new.USRP_PERM1, :new.USRP_PERM2, :new.USRP_PERM3, :new.USRP_PERM4, :new.USRP_PERM5, :new.USRP_PERM6, :new.USRP_CHAR1, :new.USRP_CHAR2, :new.USRP_CHAR3, :new.USRP_CHAR4, :new.USRP_CHAR5, :new.USRP_CHAR6,
     :new.USRP_QTY1, :new.USRP_QTY2, :new.USRP_CRT_BY, :new.USRP_CRT_DATE, :new.USRP_MOD_TIMES, :new.USRP_MOD_BY, :new.USRP_MOD_DATE, :new.USRP_UPLOAD, :new.USRP_VIEW, :new.USRP_DOWNLOAD, :new.USRP_VIEW_HIST);
   end if;

   if Deleting then
     delete from huinan.usrp_det@huinanerp where usrp_user = :old.usrp_user;
   end if;

   if Updating then
     update huinan.usrp_det@huinanerp set
            usrp_run         = :new.usrp_run,
            usrp_insert      = :new.usrp_insert,
            usrp_modify      = :new.usrp_modify,
            usrp_delete      = :new.usrp_delete,
            usrp_print       = :new.usrp_print,
            usrp_export      = :new.usrp_export,
            usrp_pst         = :new.usrp_pst,
            usrp_unpst       = :new.usrp_unpst,
            usrp_chk         = :new.usrp_chk,
            usrp_unchk       = :new.usrp_unchk,
            usrp_wf_submit   = :new.usrp_wf_submit,
            usrp_wf_cancel   = :new.usrp_wf_cancel,
            usrp_view_cost   = :new.usrp_view_cost,
            usrp_view_others = :new.usrp_view_others,
            usrp_mod_others  = :new.usrp_mod_others,
            usrp_del_others  = :new.usrp_del_others,
            usrp_design      = :new.usrp_design,
            usrp_negative    = :new.usrp_negative,
            usrp_perm1       = :new.usrp_perm1,
            usrp_perm2       = :new.usrp_perm2,
            usrp_perm3       = :new.usrp_perm3,
            usrp_perm4       = :new.usrp_perm4,
            usrp_perm5       = :new.usrp_perm5,
            usrp_perm6       = :new.usrp_perm6,
            usrp_char1       = :new.usrp_char1,
            usrp_char2       = :new.usrp_char2,
            usrp_char3       = :new.usrp_char3,
            usrp_char4       = :new.usrp_char4,
            usrp_char5       = :new.usrp_char5,
            usrp_char6       = :new.usrp_char6,
            usrp_qty1        = :new.usrp_qty1,
            usrp_qty2        = :new.usrp_qty2,
            usrp_mod_times   = usrp_mod_times + 1,
            usrp_mod_date    = sysdate,
            usrp_upload      = :new.usrp_upload,
            usrp_view        = :new.usrp_view,
            usrp_download    = :new.usrp_download,
            usrp_view_hist   = :new.usrp_view_hist
     where 1=1
     and usrp_user = :old.usrp_user
     and usrp_prog = :old.usrp_prog;
   end if;
   
   commit;
end TRI_USRP_DET_SYNC;

转载于:https://www.cnblogs.com/su1643/p/6567472.html