TRIGGER BASE -sample

CREATE TRIGGER TRIGGER_DELETE_JOJIK_TBL
AFTER UPDATE OR INSERT ON INSA_DEPT_TBL
FOR EACH ROW

DECLARE
 nowymd VARCHAR2(8);
 nowcnt INTEGER;

BEGIN
 IF UPDATING THEN
  
  SELECT
   TO_CHAR(SYSDATE, 'YYYYMMD')
   INTO nowymd
  FROM DUAL ;
  SELECT
   COUNT(*)
   INTO nowcnt
  FROM jojik_tbl
  WHERE dept_code= :NEW.org_cd;
  
  IF TRIM(:NEW.end_ymd) = '' THEN
   UPDATE jojik_tbl SET
    dept_code = :NEW.org_cd
   WHERE dept_code = :NEW.org_cd ;
   IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO jojik_tbl VALUES (:NEW.org_cd, :NEW.direct_org, :NEW.org_nm, :NEW.org_rank,'','','','') ;
   END IF ;
  ELSE
   IF nowymd < :NEW.end_ymd THEN
    UPDATE jojik_tbl SET
     dept_code = :NEW.org_cd
    WHERE dept_code = :NEW.org_cd ;
    IF SQL%ROWCOUNT = 0 THEN
     INSERT INTO jojik_tbl VALUES (:NEW.org_cd, :NEW.direct_org, :NEW.org_nm, :NEW.org_rank,'','','','') ;
    END IF ;
   ELSE
    DELETE FROM jojik_tbl WHERE dept_code= :OLD.org_cd;
   END IF ;
  END IF;
  
  IF nowcnt = 0 THEN
   INSERT INTO jojik_tbl VALUES (:NEW.org_cd, :NEW.direct_org, :NEW.org_nm, :NEW.org_rank,'','','','') ;
  END IF ;
 END IF ;

EXCEPTION WHEN OTHERS THEN
 RAISE_APPLICATION_ERROR(-20002, SQLERRM); 

END;
/

 

 

 

 

 

##sample 2

CREATE OR REPLACE TRIGGER "USRAHMS".OT_H_K_EDU_CODE_PRICE
BEFORE INSERT OR UPDATE OR DELETE ON h_k_edu_code
REFERENCING
 NEW AS NEW
 OLD AS OLD
FOR EACH ROW
DECLARE
 new_rec  h_k_edu_code_price%ROWTYPE;
 old_rec  h_k_edu_code_price%ROWTYPE;
BEGIN
 IF INSERTING OR UPDATING THEN
  new_rec.pc_code := :NEW.pc_code;
  --오늘일자
  SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')
   INTO new_rec.ymd
  FROM DUAL ;
  new_rec.price := :NEW.price;
  
  old_rec.pc_code := :OLD.pc_code;
  --기존입력일자
  SELECT MAX(ymd)
   INTO old_rec.ymd
  FROM h_k_edu_code_price
  WHERE pc_code = :OLD.pc_code ;
  old_rec.price := :OLD.price;
  
  UPDATE h_k_edu_code_price SET
   price = :NEW.price
  WHERE pc_code = :NEW.pc_code
  AND ymd = TO_CHAR(SYSDATE, 'YYYYMMDD') ;
  
  -- Update 건수 0이면 INSERT
  IF SQL%ROWCOUNT = 0 THEN
   INSERT INTO h_k_edu_code_price (
    pc_code, ymd, price
   ) VALUES (
    :NEW.price, TO_CHAR(SYSDATE, 'YYYYMMDD'), :NEW.price
   );
  END IF ;
 
 ELSIF DELETING THEN
  DELETE FROM h_k_edu_code_price
  WHERE pc_code = :OLD.pc_code ;
 END IF;
END;

강신윤 홈피에서 퍼왔어요.