Oracle 通过触发器 来创建 同步临时表 及处理 通过 自治事务 来解决 查询 基表的问题

create or replace trigger tr_sync_BD_MARBASCLASS
after INSERT or UPDATE
on BD_MARBASCLASS for each row
declare v_cnt integer;
PRAGMA AUTONOMOUS_TRANSACTION;   -- 自治事务
begin
    /**
     * Name : tr_sync_BD_MARBASCLASS
     * Desc : 物料分类 增量同步 触发器
     * Author : wuyf  11222337@qq.com
     * Date : 2014-09-10
     */

 
     case
       when inserting then
            insert into BD_MARBASCLASS_SYNC (AVERAGECOST,    AVERAGEMMAHEAD,    AVERAGEPURAHEAD,    AVGPRICE,    CODE,    CREATIONTIME,    CREATOR,    DATAORIGINFLAG,    DEF1,    DEF10,    DEF2,    DEF3,    DEF4,    DEF5,    DEF6,    DEF7,    DEF8,    DEF9,    DR,    ENABLESTATE,    INNERCODE,    MODIFIEDTIME,    MODIFIER,    NAME,    NAME2,    NAME3,    NAME4,    NAME5,    NAME6,    PK_GROUP,    PK_MARASSTFRAME,    PK_MARBASCLASS,    PK_ORG,    PK_PARENT,    TS
                   ,    PARENT_PK,    PARENT_CODE,    PARENT_NAME
                   ,ITF_CREATE_DATE ,ITF_CRUD,ITF_ISSYNC,ITF_MSG,ITF_SYNC_DATE,ITF_COUNT,ITF_PK)
            SELECT :new.AVERAGECOST,    :new.AVERAGEMMAHEAD,    :new.AVERAGEPURAHEAD,    :new.AVGPRICE,    :new.CODE,    :new.CREATIONTIME,    :new.CREATOR,    :new.DATAORIGINFLAG,    :new.DEF1,    :new.DEF10,    :new.DEF2,    :new.DEF3,    :new.DEF4,    :new.DEF5,    :new.DEF6,    :new.DEF7,    :new.DEF8,    :new.DEF9,    :new.DR,    :new.ENABLESTATE,    :new.INNERCODE,    :new.MODIFIEDTIME,    :new.MODIFIER,    :new.NAME,    :new.NAME2,    :new.NAME3,    :new.NAME4,    :new.NAME5,    :new.NAME6,    :new.PK_GROUP,    :new.PK_MARASSTFRAME,    :new.PK_MARBASCLASS,    :new.PK_ORG,    :new.PK_PARENT,    :new.TS
                   ,    MC.PK_MARBASCLASS AS PARENT_PK,    MC.CODE AS PARENT_CODE,    MC.NAME AS PARENT_NAME          -- 父级 PK,编码,名称
                   ,TO_CHAR( sysdate,'yyyy-MM-dd hh:mm:ss'),'C',0,'',null,0,CAST( sys_guid() AS NVARCHAR2(36))
                   FROM (
                        SELECT :new.PK_PARENT AS PK_PARENT 
                               FROM DUAL
                   ) T
                   LEFT JOIN BD_MARBASCLASS MC     -- 物料分类(父级)
                        ON T.PK_PARENT = MC.PK_MARBASCLASS
                   ;
       else 
            insert into BD_MARBASCLASS_SYNC (AVERAGECOST,    AVERAGEMMAHEAD,    AVERAGEPURAHEAD,    AVGPRICE,    CODE,    CREATIONTIME,    CREATOR,    DATAORIGINFLAG,    DEF1,    DEF10,    DEF2,    DEF3,    DEF4,    DEF5,    DEF6,    DEF7,    DEF8,    DEF9,    DR,    ENABLESTATE,    INNERCODE,    MODIFIEDTIME,    MODIFIER,    NAME,    NAME2,    NAME3,    NAME4,    NAME5,    NAME6,    PK_GROUP,    PK_MARASSTFRAME,    PK_MARBASCLASS,    PK_ORG,    PK_PARENT,    TS
                   ,    PARENT_PK,    PARENT_CODE,    PARENT_NAME
                   ,ITF_CREATE_DATE ,ITF_CRUD,ITF_ISSYNC,ITF_MSG,ITF_SYNC_DATE,ITF_COUNT,ITF_PK)
            SELECT :new.AVERAGECOST,    :new.AVERAGEMMAHEAD,    :new.AVERAGEPURAHEAD,    :new.AVGPRICE,    :new.CODE,    :new.CREATIONTIME,    :new.CREATOR,    :new.DATAORIGINFLAG,    :new.DEF1,    :new.DEF10,    :new.DEF2,    :new.DEF3,    :new.DEF4,    :new.DEF5,    :new.DEF6,    :new.DEF7,    :new.DEF8,    :new.DEF9,    :new.DR,    :new.ENABLESTATE,    :new.INNERCODE,    :new.MODIFIEDTIME,    :new.MODIFIER,    :new.NAME,    :new.NAME2,    :new.NAME3,    :new.NAME4,    :new.NAME5,    :new.NAME6,    :new.PK_GROUP,    :new.PK_MARASSTFRAME,    :new.PK_MARBASCLASS,    :new.PK_ORG,    :new.PK_PARENT,    :new.TS
                   ,    MC.PK_MARBASCLASS AS PARENT_PK,    MC.CODE AS PARENT_CODE,    MC.NAME AS PARENT_NAME          -- 父级 PK,编码,名称
                   ,TO_CHAR( sysdate,'yyyy-MM-dd hh:mm:ss'),'U',0,'',null ,0,CAST( sys_guid() AS NVARCHAR2(36))
                   FROM (
                        SELECT :new.PK_PARENT AS PK_PARENT 
                               FROM DUAL
                   ) T
                   LEFT JOIN BD_MARBASCLASS MC     -- 物料分类(父级)
                        ON T.PK_PARENT = MC.PK_MARBASCLASS
                   ;
     end case;
     
     commit;  -- 记得 提交 事务
end;

--判断表是否存在，如果存在则删除
declare 
      num   number; 
begin 
      select count(1) into num from all_tables where TABLE_NAME = 'BD_MARBASCLASS_SYNC' and OWNER='NC'; 
      if   num=1   then 
          execute immediate 'drop table BD_MARBASCLASS_SYNC'; 
      end   if; 
end; 

/
/**
 * Name : BD_MARBASCLASS_SYNC
 * Desc : 物料分类 同步表
 * Author : wuyf  11222337@qq.com
 * Date : 2014-09-10
 */ 
create table BD_MARBASCLASS_SYNC as 
select t.*
       , CAST(NULL AS nvarchar2(19)) AS ITF_CREATE_DATE         -- 同步记录创建时间
       , CAST('' AS NVARCHAR2(30)) AS ITF_CRUD                  -- 同步类型 : C:新增,U:更新,D:删除,R:读(一般没有)
       , 0 AS ITF_ISSYNC                                        -- 是否已同步: 0:未同步或同步失败, 1:已同步成功
       , CAST('' AS NVARCHAR2(2000)) AS ITF_MSG                 -- 同步消息: 
       , CAST(NULL AS nvarchar2(19)) AS ITF_SYNC_DATE           -- 最后同步时间
       , 0 AS ITF_COUNT                                         -- 同步次数 
       ,CAST( sys_guid() AS NVARCHAR2(36)) AS ITF_PK -- 同步主键
       from (
          SELECT 
                 MC.*
                 ,nvl(MCP.PK_MARBASCLASS,'') AS PARENT_PK    -- 父分类PK
                 ,nvl(MCP.CODE,'') AS PARENT_CODE            -- 父分类编码
                 ,nvl(MCP.NAME,'') AS PARENT_NAME            -- 父分类名称
                 FROM BD_MARBASCLASS MC
                 LEFT JOIN BD_MARBASCLASS MCP
                      ON MC.PK_PARENT = MCP.PK_MARBASCLASS
       ) t 
       where 1=0;