暫存資料表利用觸發器同步到業務表

標籤：

/*  暫存資料表同步到業務表的觸發器    INF_EMPLOYEE_INTF 使用者暫存資料表  INF_EMPLOYEE  業務線表*/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   --如果此次新增ID在業務表有則update 否則 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                 = :NEW.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_CATEGORY_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.PAIC_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,       MAJOR,       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,       XLATSHORTNAME,       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_CODE,       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_MANAGER_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_BUSINESS_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_DESC,         :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, --系統錯誤碼       ERROR_MESSAGE, --系統錯誤資訊       TRIGGER_NAME, --出錯的trigger       TRIGGER_USER, --出錯的使用者       TRIGGER_DATE, --出錯的時間       ERROR_COMMENT --出錯詳細資料       )    VALUES      (V_SQLCODE,       V_SQLERRM,       ‘INF_EMPLOYEE_INTF_BI‘,       ‘PADINFODATA‘,       SYSDATE,       V_ERROR_COMMENT);END;

　　

暫存資料表利用觸發器同步到業務表