ORACLE TRIGGER를 CUBRID TRIGGER로 변환하기

ORACLE TRIGGER를 CUBRID로 변환한 예제이다.

특이 사항은  Oracle의 경우 한 TRIGGER 내에 여러 SQL를 사용할 수 있지만 CUBRID는 각각 분리하여 작성해야 한다.

또한 INSERT OR UPDATE로 생성된 TRIGGER도 각각 분리해야 한다.

<Oracle Trigger>

create or replace TRIGGER SOA.TR02_TN_SVC_PRCUSE_REQST_INFO

 AFTER    

    UPDATE ON SOA.TN_PUBR_SVC_PRCUSE_REQST_INFO         

    FOR EACH ROW

DECLARE

      PRAGMA AUTONOMOUS_TRANSACTION;  

      V_INSTT_CODE VARCHAR2(40) := '';

BEGIN

        SELECT INSTT_CODE INTO V_INSTT_CODE

        FROM    TN_PUBR_PUBLIC_DATA_DETAIL

        WHERE   PUBLIC_DATA_DETAIL_PK = :NEW.PUBLIC_DATA_DETAIL_PK;

    IF :NEW.DECSN_MATTER_CODE = 'PSDE02' AND (:NEW.PRCUSE_LMTT_CODE != 'PRCU01' OR :NEW.PRCUSE_LMTT_CODE IS NULL) AND V_INSTT_CODE = '9750000' THEN 

        -- 승인시 신규 회원테이블에 입력한다.          

            INSERT INTO SOAC.TN_PUBR_ATCH_FILE_MANAGE_CI

              (PBLONSIP_RESRCE_PK,

                  SEQ_NO,

                  ATCH_FILE_TY_CODE,

                  ATCH_FILE_ID,

                  ATCH_FILE_USE_AT)

           SELECT PUBLIC_DATA_DETAIL_PK,

                   SEQ_NO,

                   ATCH_FILE_TY_CODE,

                   ATCH_FILE_ID,

                   ATCH_FILE_USE_AT

             FROM SOA.TN_PUBR_ATCH_FILE_MANAGE

            WHERE PUBLIC_DATA_DETAIL_PK = :NEW.PUBLIC_DATA_DETAIL_PK;

        -- 오퍼레이션 권한

        INSERT INTO SOAC.TN_PUBR_OPRTIN_AUTHOR_CI

        SELECT  *

        FROM    SOA.TN_PUBR_OPRTIN_AUTHOR

        WHERE   PUBLIC_DATA_DETAIL_PK = :NEW.PUBLIC_DATA_DETAIL_PK

            AND MBER_ID = :NEW.MBER_ID;

     (이하 생략)

<CUBRID TRIGGER>

--create or replace TRIGGER SOA.TR02_TN_SVC_PRCUSE_REQST_INFO

create TRIGGER TR02_TN_SVC_PRCUSE_REQST_INFO1

AFTER UPDATE ON TN_PUBR_SVC_PRCUSE_REQST_INFO

--FOR EACH ROW

--DECLARE

--      PRAGMA AUTONOMOUS_TRANSACTION;

--      V_INSTT_CODE VARCHAR2(40) := '';

--BEGIN

--        SELECT INSTT_CODE INTO V_INSTT_CODE

--        FROM    TN_PUBR_PUBLIC_DATA_DETAIL

--        WHERE   PUBLIC_DATA_DETAIL_PK = :NEW.PUBLIC_DATA_DETAIL_PK;

    IF obj.DECSN_MATTER_CODE = 'PSDE02' AND (obj.PRCUSE_LMTT_CODE != 'PRCU01' OR obj.PRCUSE_LMTT_CODE IS NULL)

       AND '9750000' = (SELECT INSTT_CODE

                        FROM TN_PUBR_PUBLIC_DATA_DETAIL

                        WHERE PUBLIC_DATA_DETAIL_PK = obj.PUBLIC_DATA_DETAIL_PK)

       EXECUTE

       -- 승인시 신규 회원테이블에 입력한다.

            INSERT INTO SOAC.TN_PUBR_ATCH_FILE_MANAGE_CI

              (PBLONSIP_RESRCE_PK,

                  SEQ_NO,

                  ATCH_FILE_TY_CODE,

                  ATCH_FILE_ID,

                  ATCH_FILE_USE_AT)

           SELECT PUBLIC_DATA_DETAIL_PK,

                   SEQ_NO,

                   ATCH_FILE_TY_CODE,

                   ATCH_FILE_ID,

                   ATCH_FILE_USE_AT

             FROM TN_PUBR_ATCH_FILE_MANAGE

            WHERE PUBLIC_DATA_DETAIL_PK = obj.PUBLIC_DATA_DETAIL_PK;

COMMIT;

--create or replace TRIGGER SOA.TR02_TN_SVC_PRCUSE_REQST_INFO

create TRIGGER TR02_TN_SVC_PRCUSE_REQST_INFO2

AFTER UPDATE ON TN_PUBR_SVC_PRCUSE_REQST_INFO

--FOR EACH ROW

--DECLARE

--      PRAGMA AUTONOMOUS_TRANSACTION;

--      V_INSTT_CODE VARCHAR2(40) := '';

--BEGIN

--        SELECT INSTT_CODE INTO V_INSTT_CODE

--        FROM    TN_PUBR_PUBLIC_DATA_DETAIL

--        WHERE   PUBLIC_DATA_DETAIL_PK = :NEW.PUBLIC_DATA_DETAIL_PK;

    IF obj.DECSN_MATTER_CODE = 'PSDE02' AND (obj.PRCUSE_LMTT_CODE != 'PRCU01' OR obj.PRCUSE_LMTT_CODE IS NULL)

       AND '9750000' = (SELECT INSTT_CODE

                        FROM TN_PUBR_PUBLIC_DATA_DETAIL

                        WHERE PUBLIC_DATA_DETAIL_PK = obj.PUBLIC_DATA_DETAIL_PK)

       EXECUTE

        -- 오퍼레이션 권한

        INSERT INTO TN_PUBR_OPRTIN_AUTHOR_CI

         --SELECT  * /* 컬럼 추가 이슈 */

        SELECT  MBER_ID

               ,PUBLIC_DATA_DETAIL_PK

               ,OPRTIN_SEQ_NO

               ,USE_REQST_NO

               ,PROCESS_STTUS_CODE

               ,PROCESS_STTUS_DC

               ,DILY_USE_EXPECT_CO

               ,DILY_USE_CHANGE_RESN

               ,USE_TY

               ,PRCUSE_PURPS

               ,PRCUSE_URL

               ,PRCUSE_PRPOS

               ,PRCUSE_AGRE_AT

               ,OPRTIN_URL

               ,AUTHOR_ENNC

               ,USE_BEGIN_DATE

               ,USE_END_DATE

               ,REGISTER_ID

               ,REGIST_DT

               ,UPDUSR_ID

               ,UPDT_DT

               ,DILY_USE_CHANGE_RESN_CODE

               ,PRCUSE_REQST_SEQ_NO

               ,PRCUSE_LMTT_CODE

               ,PRCUSE_LMTT_RESN

        FROM    TN_PUBR_OPRTIN_AUTHOR

        WHERE   PUBLIC_DATA_DETAIL_PK = obj.PUBLIC_DATA_DETAIL_PK

            AND MBER_ID = obj.MBER_ID;

COMMIT;