CREATE OR REPLACE TRIGGER COMM.Trg_Member AFTER INSERT OR UPDATE OR DELETE ON MEMBER
FOR EACH ROW
/*********************************************
Date : 2008년 02월 22일
Autho : Chung, Woo-Chul (chul@nissoft.co.kr)
***********************************************/
DECLARE
vi_User_id VARCHAR2(20);
vi_User_ssn VARCHAR2(14);
BEGIN
IF (INSERTING) THEN
vi_User_id := :OLD.USER_ID;
vi_User_ssn := :OLD.USER_SSN;
INSERT INTO MEMBER ( USER_ID
, USER_PASSWORD
, USER_NAME
, USER_EMAIL
, USER_NICKNAME
, USER_SSN
, USER_GENDER
, USER_BDATE
, USER_BDATE_LUNAR_FG
, USER_TELEPHONE
, USER_MOBILE_PHONE
, USER_ZIPCODE
, USER_ADDRESS_PREFIX
, USER_ADDRESS_POSTFIX
, USER_REGISTER_DATE
, RNAME_CLSF )
SELECT USER_ID,
USER_PASSWORD,
USER_NAME,
USER_EMAIL,
USER_NICKNAME,
USER_SSN,
USER_GENDER,
USER_BDATE,
USER_BDATE_L,
USER_TELEPHONE,
USER_MOBILE_PHONE,
USER_ZIPCODE,
USER_ADDR,
USER_ADDR_DETAIL,
REG_DATE,
RNAME_CLSF
FROM MEMBER WHERE USER_ID = vi_User_id AND USER_SSN = vi_User_ssn;
ELSIF (UPDATING) THEN
vi_User_id := :OLD.USER_ID;
vi_User_ssn := :OLD.USER_SSN;
UPDATE MEMBER SET
USER_PASSWORD = :NEW.USER_PASSWORD
, USER_NAME = :NEW.USER_NAME
, USER_EMAIL = :NEW.USER_EMAIL
, USER_NICKNAME = :NEW.USER_NICKNAME
, USER_GENDER = :NEW.USER_GENDER
, USER_BDATE = :NEW.USER_BDATE
, USER_BDATE_LUNAR_FG = :NEW.USER_BDATE_L
, USER_TELEPHONE = :NEW.USER_TELEPHONE
, USER_MOBILE_PHONE = :NEW.USER_MOBILE_PHONE
, USER_ZIPCODE = :NEW.USER_ZIPCODE
, USER_ADDRESS_PREFIX = :NEW.USER_ADDR
, USER_ADDRESS_POSTFIX = :NEW.USER_ADDR_DETAIL
, USER_REGISTER_DATE = :NEW.LAST_UPDATE
, RNAME_CLSF = :NEW.RNAME_CLSF
WHERE USER_ID = vi_User_id AND USER_SSN = vi_User_ssn;
ELSIF (DELETING) THEN
vi_User_id := :OLD.USER_ID;
vi_User_ssn := :OLD.USER_SSN;
INSERT INTO MEMBER_OUT SELECT * FROM MEMBER WHERE USER_ID = vi_User_id AND USER_SSN = vi_User_ssn;
END IF;
END Trg_Member;