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;
