【oracle】触发器

一、关于触发器的使用

CREATE OR REPLACE TRIGGER mes_ima_trg
after Insert OR Delete OR Update
  ON IMA_FILE
  REFERENCING new as n old as o
  FOR EACH ROW
DECLARE
   l_erp_status varchar2(3);
   l_tran_status varchar2(3);
   l_timestamp varchar2(17);
   l_cnt number;
   l_userenv varchar2(10);
   l_entid varchar2(10);
   l_imaacti_o varchar2(1);
   l_imaacti_n varchar2(1);
   l_sid varchar2(30);
   l_user varchar2(30);
   l_cnt2 number;

BEGIN
   SELECT USERENV('SESSIONID'),USERENV('SID'),USER INTO l_userenv,l_sid,l_user  FROM dual;
   l_entid := l_user;

   -- Trigger Code begin
   SELECT TO_CHAR(sysdate,'yyyyMMddhh24miss') || SUBSTR(TO_CHAR(sysdate,'SSSSS'),1,3)
   INTO l_timestamp FROM dual;

   SELECT count(*) INTO l_cnt FROM tra_ima_file WHERE tra_imaplant = l_entid
      AND tra_ima01 = :n.ima01 AND tra_imatran_time = l_timestamp;
   /**如果產生相同的一筆資料，必需刪除 **/
   IF (l_cnt > 0) THEN
      DELETE tra_ima_file WHERE tra_imaplant = l_entid AND tra_ima01 = :n.ima01 AND tra_imatran_time = l_timestamp;
   END IF;

   l_imaacti_o := :o.imaacti;
   l_imaacti_n := :n.imaacti;

   CASE
      WHEN inserting THEN
         l_erp_status := 'A';
         l_tran_status := 'AU';

      WHEN updating THEN
         l_erp_status := 'U';
         l_tran_status := 'AU';
         /**確認碼為由Y，或者由Y變更為P，才進行新增，若為P更改為D再進行寫入 **/
         IF (l_imaacti_o = 'Y' AND (l_imaacti_n = 'P' OR l_imaacti_n = 'N')) OR (l_imaacti_n = 'Y' ) THEN
            IF l_imaacti_n = 'P' OR l_imaacti_n = 'N' THEN
               l_imaacti_n := 'N';
               l_tran_status := 'D';
            END IF;

            INSERT INTO tra_ima_file (tra_imaplant,tra_ima01,tra_ima08,tra_imamodu,tra_imatran_time, tra_imastatus, tra_imatran_status,tra_imaacti)
               VALUES (l_entid,:n.ima01,:n.ima08,:n.imamodu,l_timestamp,l_erp_status,l_tran_status,l_imaacti_n);
         END IF;

      WHEN deleting THEN
         l_erp_status := 'D';
         l_tran_status := 'D';

   END CASE;
   -- Trigger Code end

EXCEPTION
WHEN OTHERS THEN
   CASE
      WHEN deleting THEN
         raise_application_error(-20100,'Trigger Error code'||SQLCODE||':'||SQLERRM||'DATAKEY tra_ima01:'||:o.ima01);
      ELSE
         raise_application_error(-20100,'Trigger Error code'||SQLCODE||':'||SQLERRM||'DATAKEY tra_ima01:'||:n.ima01);
   END CASE;
END;