Temporary tables use triggers to synchronize to business tables

/* Temp table sync to Business table trigger inf_employee_intf user Temp Table Inf_employee line of Business table */create OR REPLACE TRIGGER Inf_employee_intf_bi before INSERT on Padinfodata. inf_employee_intf for each ROW when (NEW.   Paic_um_num is not NULL) DECLARE v_umcount number (10);   V_empcount number (10);   V_sqlcode VARCHAR2 (6);   V_SQLERRM VARCHAR2 (200);   V_error_comment VARCHAR2 (300); v_employee_id Padinfodata. inf_employee_intf. Employee_id%type; BEGIN--If this new ID has an update in the business table then insert SELECT COUNT (0) into V_umcount from Padinfodata. Inf_employee E WHERE e.paic_um_num =: NEW.   Paic_um_num; IF v_umcount > 0 Then UPDATE inf_employee A SET a.paic_empno =: NEW. Paic_empno, A.id_card_type_code =: new.id_card_type_code, A.paic_um_num =: N EW.           Paic_um_num, A.id_card =: New.id_card, A.name =: New.name,    A.person_status_code      =: NEW. Person_status_code, a.org_id =: new.org_id, A.org_name =: NEW. Org_name, A.lot_num =: NEW. Lot_num, a.onboard_date =: NEW. Onboard_date, a.birth_date =: NEW. Birth_date, A.is_rehire =: new.is_rehire, a.last_on_board_date =: NEW. Last_on_board_date, a.address =: NEW. ADDRESS, A.phone =: NEW. PHONE, A.party =: NEW. Party, A.birth_place =: NEW. Birth_place, A.school =: NEW. SCHOOL, A.major =: NEW. MAJOR, A.education_level_code =: NEW. Education_level_code, A.has_child =: NEW. Has_child, a.team_leader_id =: NEW.  TEAM_LEADER_ID, A.team_leader_empno         =: NEW. Team_leader_empno, A.team_leader_um =: NEW. Team_leader_um, A.team_leader_name =: NEW. Team_leader_name, a.manager_id =: NEW. manager_id, A.manager_empno =: NEW. Manager_empno, A.manager_um =: NEW. Manager_um, A.manager_name =: NEW. Manager_name, A.series_code =: NEW. Series_code, A.category_code =: NEW. Category_code, a.position_type_id =: NEW. position_type_id, A.position_type_desc =: NEW. Position_type_desc, a.position_id =: NEW. position_id, A.position_desc =: NEW. Position_desc, A.empl_class =: NEW. Empl_class, A.empl_class_desc =: NEW. Empl_class_desc, A.nw_desc =: NEW. Nw_desc, A.mar_status = : NEW. Mar_status, A.paic_marists_desc =: NEW. Paic_marists_desc, A.sex_code =: NEW. Sex_code, A.recruit_channel_desc =: NEW. Recruit_channel_desc, A.recruit_source_desc =: NEW. Recruit_source_desc, A.PAIC_NW_SW =: NEW. PAIC_NW_SW, A.xlatshortname =: NEW. Xlatshortname, A.place_code =: NEW. Place_code, A.tmr_business_mode_code =: NEW. Tmr_business_mode_code, A.tmr_business_mode_desc =: NEW. Tmr_business_mode_desc, A.th_product_type_code =: New.th_product_type_code, A.hy_business_categ           Ory_code =: New.hy_business_category_code, A.hy_business_subcate_code =: New.hy_business_subcate_code, A.pc_item_code =: NEW. Pc_item_code, A.pc_item_desc =: NEW. Pc_item_desc, A.tmr_category_code =: NEW. Tmr_category_coDE, A.tmr_category_desc =: NEW. Tmr_business_mode_desc, A.education_level_desc =: NEW. Education_level_code, a.online_date =: NEW. Online_date, A.party_role_code =: NEW. Party_role_code, A.party_role_desc =: NEW. Party_role_desc, a.created_by =: NEW. created_by, a.created_date =: NEW. Created_date, a.updated_by =: NEW. updated_by, a.updated_date =: NEW. Updated_date, A.place_desc =: NEW.  Place_desc, A.th_product_type_desc =: new.th_product_type_desc, A.hy_business_category_desc = : New.hy_business_category_desc, A.hy_business_subcate_desc =: New.hy_business_subcate_desc, A.CTI_ CODE =: NEW. Cti_code, A.tel_no =: new.tel_no, A.platform_code = : NEW. Platform_code, A.staff_no =: NEW. Staff_no, A.series_desc =: NEW. Series_desc, A.category_desc =: NEW. Category_desc, a.b_created_date =: NEW. B_created_date, a.b_updated_date =: NEW. B_updated_date, A.tmr_skill_lv_life =: NEW. Tmr_skill_lv_life, A.agent_certifi_num =: NEW. Agent_certifi_num, A.date_certifi_valid =: NEW. Date_certifi_valid, A.xb_tmr_biz_mode_code =: NEW. Xb_tmr_biz_mode_code, A.xb_work_place_code =: NEW. Xb_work_place_code, A.call_display_number =: NEW. Call_display_number, a.zone_manager_id =: NEW. zone_manager_id, a.zone_manager_emp =: NEW. Zone_manager_emp, A.zone_manager_um =: NEW. Zone_manager_um, A.zone_manager_name =: NEW. Zone_manager_name, A.Ucp_code =: NEW. Ucp_code, A.cts_product_code =: NEW. Cts_product_code, A.cts_tmr_function_code =: NEW. Cts_tmr_function_code, A.operation_type =: NEW. Operation_type, a.tfzx_id =: NEW. tfzx_id, A.tfzx_name =: NEW. Tfzx_name, a.tfqy_id =: NEW. tfqy_id, A.tfqy_name =: NEW. Tfqy_name, a.tcqy_id =: NEW. tcqy_id, A.tcqy_name =: NEW. Tcqy_name, a.tdep_id =: NEW. tdep_id, A.tdep_name =: NEW. Tdep_name, a.egrp_id =: NEW. egrp_id, A.egrp_name =: NEW. Egrp_name, A.account_manager =: NEW. Account_manager, A.account_manager_level =: NEW. Account_manager_level, A.disa_recovery_platform_code =: NEW. Disa_rEcovery_platform_code, A.disa_recovery_cti_code =: NEW. Disa_recovery_cti_code, A.source_from = ' TJS ' WHERE a.paic_um_num =: New.pai   C_um_num; ELSE SELECT COUNT (0) into V_empcount from Padinfodata. Inf_employee E WHERE e.employee_id =: NEW.      employee_id; v_employee_id: =: NEW.      employee_id;      IF v_empcount > 0 Then v_employee_id: =-1 * v_employee_id;      END IF;       INSERT into Inf_employee (employee_id, Paic_empno, Paic_um_num, Id_card_type_code, Id_card,       NAME, Person_status_code, org_id, Org_name, Lot_num, Onboard_date, Birth_date, Is_rehire, Last_on_board_date, ADDRESS, PHONE, Party, Birth_place, SCHOOL, M       Ajor, Education_level_code, Has_child, team_leader_id, Team_leader_empno, Team_leader_um, Team_leader_name, manager_id, Manager_empno,       Manager_um, Manager_name, Series_code, Category_code, position_type_id, Position_type       _desc, position_id, Position_desc, Empl_class, Empl_class_desc, Nw_desc, Mar_status, Paic_marists_desc, Sex_code, Recruit_channel_desc, Recruit_source_desc, PAIC_NW_SW, XLA       Tshortname, Place_code, Tmr_business_mode_code, Tmr_business_mode_desc, Th_product_type_code, Hy_business_category_code, Hy_business_subcate_code, Pc_item_code, Pc_item_desc, Tmr_category_co       DE, Tmr_category_desc, Education_level_desc, Online_date, Party_role_code, Party_role_desc,       Created_by, Created_date, updated_by, Updated_date, Place_desc, Th_product_type_desc,       Hy_business_category_desc, Hy_business_subcate_desc, Cti_code, Tel_no, Platform_code, Staff_no, Series_dESC, Category_desc, B_created_date, B_updated_date, Tmr_skill_lv_life, Agent_certifi_num, Date_certifi_valid, Xb_tmr_biz_mode_code, Xb_work_place_code, Call_display_number, ZONE_MANAGE       r_id, Zone_manager_emp, Zone_manager_um, Zone_manager_name, Ucp_code, Cts_product_code, Cts_tmr_function_code, Operation_type, tfzx_id, Tfzx_name, tfqy_id, Tfqy_name, Tcqy_ ID, Tcqy_name, tdep_id, Tdep_name, egrp_id, Egrp_name, Account_manager, Account_ Manager_level, Disa_recovery_platform_code, Disa_recovery_cti_code, Source_from) VALUES (v_ employee_id,: NEW. Paic_empno,: NEW. Paic_um_num,: New.id_card_type_code,: New.id_card,: New.name,: NEW. Person_status_code,: new.org_id,: New.org_name,: NEW. Lot_num,: NEW.      Onboard_date,   : NEW. Birth_date,: New.is_rehire,: NEW. Last_on_board_date,: NEW. ADDRESS,: NEW. PHONE,: NEW. Party,: NEW. Birth_place,: NEW. SCHOOL,: NEW. MAJOR,: NEW. Education_level_code,: NEW. Has_child,: NEW. team_leader_id,: NEW. Team_leader_empno,: NEW. Team_leader_um,: NEW. Team_leader_name,: NEW. manager_id,: NEW. Manager_empno,: NEW. Manager_um,: NEW. Manager_name,: NEW. Series_code,: NEW. Category_code,: NEW. position_type_id,: NEW. Position_type_desc,: NEW. position_id,: NEW. Position_desc,: NEW. Empl_class,: NEW. Empl_class_desc,: NEW. Nw_desc,: NEW. Mar_status,: NEW. Paic_marists_desc,: NEW. Sex_code,: NEW. Recruit_channel_desc,: NEW. Recruit_source_desc,: NEW. PAIC_NW_SW,: NEW. Xlatshortname,: NEW. Place_code,: NEW. Tmr_business_mode_coDE,: NEW. Tmr_business_mode_desc,: New.th_product_type_code,: New.hy_business_category_code,: NEW.HY_BUSINES S_subcate_code,: NEW. Pc_item_code,: NEW. Pc_item_desc,: NEW. Tmr_category_code,: NEW. Tmr_business_mode_desc,: NEW. Education_level_code,: NEW. Online_date,: NEW. Party_role_code,: NEW. Party_role_desc,: NEW. Created_by,: NEW. Created_date,: NEW. Updated_by,: NEW. Updated_date,: NEW. Place_desc,: New.th_product_type_desc,: New.hy_business_category_desc,: New.hy_business_subcate_de SC,: NEW. Cti_code,: New.tel_no,: NEW. Platform_code,: NEW. Staff_no,: NEW. Series_desc,: NEW. Category_desc,: NEW. B_created_date,: NEW. B_updated_date,: NEW. Tmr_skill_lv_life,: NEW. Agent_certifi_num,: NEW. Date_certifi_valid,: NEW. Xb_tmr_biz_mode_code,: NEW. Xb_worK_place_code,: NEW. Call_display_number,: NEW. zone_manager_id,: NEW. Zone_manager_emp,: NEW. Zone_manager_um,: NEW. Zone_manager_name,: NEW. Ucp_code,: NEW. Cts_product_code,: NEW. Cts_tmr_function_code,: NEW. Operation_type,: NEW. tfzx_id,: NEW. Tfzx_name,: NEW. tfqy_id,: NEW. Tfqy_name,: NEW. tcqy_id,: NEW. Tcqy_name,: NEW. tdep_id,: NEW. Tdep_name,: NEW. egrp_id,: NEW. Egrp_name,: NEW. Account_manager,: NEW. Account_manager_level,: NEW. Disa_recovery_platform_code,: NEW.  Disa_recovery_cti_code, ' TJS ');  END IF;    EXCEPTION when OTHERS then v_sqlcode: = SQLCODE;    V_SQLERRM: = SUBSTR (SQLERRM, 1, 200); V_error_comment: = ' Merge inf_employee umId: ' | |    : New.paic_um_num; INSERT into Padinfodata.tr_error_log (error_no,--system error code error_message,--system error message trigger_name,--Error Trigg ER trigger_usER,--Error user trigger_date,--Error time Error_comment--error details) VALUES (V_sqlcode, V_SQLERRM, ' Inf_employee_intf_bi ', ' padinfodata ', sysdate, v_error_comment); END;

　　

Temporary tables use triggers to synchronize to business tables