질의작성

ORACLE TRIGGER를 CUBRID TRIGGER로 변환하기

by 김창휘 posted Dec 31, 2015

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;



Articles

1 2 3 4 5 6 7 8 9 10