create or replace trigger tr_sync_BD_MARBASCLASSafter INSERT or UPDATEon BD_MARBASCLASS for each rowdeclare 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;