Oracle creates synchronization temporary tables through triggers and handles problems with query base tables through autonomous transactions

Trigger

Create or Replace TriggerTr_sync_bd_marbasclassafterINSERT or UPDATE onBd_marbasclass forEach rowDeclareV_cntinteger;   PRAGMA autonomous_transaction; --Autonomous Transactionsbegin    /** * Name:tr_sync_bd_marbasclass * DESC: Material classification Incremental Sync trigger * AUTHOR:WUYF [email protected] * Date:2 014-09-10*/       Case        whenInserting Then            Insert  intoBd_marbasclass_sync (Averagecost, Averagemmahead, Averagepurahead, Avgprice, CODE, CreationTime, CR Eator, 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, PAR            Ent_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 asPARENT_PK, MC. CODE asParent_code, Mc.name asParent_name--parent PK, encoding, name, To_char (Sysdate,'YYYY-MM-DD Hh:mm:ss'),'C',0,"',NULL,0,CAST(Sys_guid () asNVARCHAR2 ( $))                    from (                        SELECT: New. Pk_parent aspk_parent fromDUAL) T Left JOINBd_marbasclass MC--Item Classification (parent)                         onT.pk_parent=MC.       Pk_marbasclass; Else             Insert  intoBd_marbasclass_sync (Averagecost, Averagemmahead, Averagepurahead, Avgprice, CODE, CreationTime, CR Eator, 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, PAR            Ent_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 asPARENT_PK, MC. CODE asParent_code, Mc.name asParent_name--parent PK, encoding, name, To_char (Sysdate,'YYYY-MM-DD Hh:mm:ss'),'U',0,"',NULL,0,CAST(Sys_guid () asNVARCHAR2 ( $))                    from (                        SELECT: New. Pk_parent aspk_parent fromDUAL) T Left JOINBd_marbasclass MC--Item Classification (parent)                         onT.pk_parent=MC.     Pk_marbasclass; End  Case; Commit; --Remember to commit the transaction End;

Create a temporary synchronization intermediate state table structure

--determine if the table exists and delete if it existsDeclareNum Number; begin       Select Count(1) intoNum fromAll_tableswheretable_name= 'Bd_marbasclass_sync'  andOWNER='NC'; ifNum=1    Then           ExecuteImmediate'drop table Bd_marbasclass_sync'; End   if; End; /
/** * Name:bd_marbasclass_sync * DESC: Material classification Synchronization table * AUTHOR:WUYF [email protected] * DATE:2014-09-10*/ Create TableBd_marbasclass_sync as SelectT.*       , CAST(NULL  asNVARCHAR2 ( +)) asItf_create_date--Synchronize record creation time,CAST("'  asNVARCHAR2 ( -)) asItf_crud--Sync Type: C: New, U: Update, D: Delete, R: Read (not normally),0  asItf_issync--synchronized: 0: Not synchronized or sync failed, 1: Synchronized successfully,CAST("'  asNVARCHAR2 ( -)) asItf_msg--To Synchronize messages:,CAST(NULL  asNVARCHAR2 ( +)) asItf_sync_date--Last Sync time,0  asItf_count--Number of synchronizations,CAST(Sys_guid () asNVARCHAR2 ( $)) asItf_pk--Synchronizing primary keys        from (          SELECTMC.*, NVL (MCP. Pk_marbasclass,"') asParent_pk--Parent category PK, NVL (MCP. CODE,"') asParent_code--Parent category Encoding, NVL (Mcp.name,"') asParent_name--Parent category name                  fromBd_marbasclass MC Left JOINBd_marbasclass MCP onMc. Pk_parent=MCP. Pk_marbasclass) Twhere 1=0;

Oracle creates synchronization temporary tables through triggers and handles problems with query base tables through autonomous transactions