自己寫的一個覺得很有價值的觸發器

 

CREATE OR REPLACE TRIGGER DBAUSER.TUPDATE_TSURPLUS
AFTER DELETE OR INSERT OR UPDATE
OF VRTMSORT
  ,AHR
ON DBAUSER.T_OVERTIME 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
countsum1 NUMBER;
countsum2 NUMBER;
/**//******************************************************************************
   NAME:       TUpdate_TSURPLUS
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2007/01/13             1. Created this trigger.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     TUpdate_TSURPLUS
      Sysdate:         2007/01/13
      Date and Time:   2007/01/13, AM 08:48:40, and 2007/01/13 AM 08:48:40
      Username:         (set in TOAD Options, Proc Templates)
      Table Name:      T_OVERTIME (set in the "New PL/SQL Object" dialog)
      Trigger Options:  (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
   countsum1:=0;
   countsum2:=0;
  IF INSERTING THEN 
       SELECT COUNT(*) INTO countsum1 FROM T_TOTALSURPLUS WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT; 
    IF countsum1>0 THEN
                UPDATE T_TOTALSURPLUS SET T_TOTALSURPLUS.SRPLS_HR=T_TOTALSURPLUS.SRPLS_HR+:NEW.AHR WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
    ELSIF countsum1=0 THEN
                INSERT INTO T_TOTALSURPLUS(WORKID,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,:NEW.VRTMSORT,:NEW.AHR);
    END IF;
  ELSIF UPDATING THEN
             IF :NEW.VRTMSORT=:OLD.VRTMSORT THEN
               IF :NEW.AHR<>:OLD.AHR THEN
                   UPDATE T_TOTALSURPLUS SET T_TOTALSURPLUS.SRPLS_HR=T_TOTALSURPLUS.SRPLS_HR+(:NEW.AHR-:OLD.AHR) WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
             END IF;
          ELSIF :NEW.VRTMSORT<>:OLD.VRTMSORT THEN
                SELECT COUNT(*) INTO countsum2 FROM T_TOTALSURPLUS WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;        
                IF countsum2>0 THEN
                            UPDATE T_TOTALSURPLUS SET T_TOTALSURPLUS.SRPLS_HR=T_TOTALSURPLUS.SRPLS_HR-:NEW.AHR WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:OLD.VRTMSORT;
                        UPDATE T_TOTALSURPLUS SET T_TOTALSURPLUS.SRPLS_HR=T_TOTALSURPLUS.SRPLS_HR+:NEW.AHR WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:NEW.VRTMSORT;
                ELSIF countsum2=0 THEN
                           UPDATE T_TOTALSURPLUS SET T_TOTALSURPLUS.SRPLS_HR=T_TOTALSURPLUS.SRPLS_HR-:NEW.AHR WHERE T_TOTALSURPLUS.WORKID=:NEW.IDS AND T_TOTALSURPLUS.VRTM_SORT=:OLD.VRTMSORT;
                          INSERT INTO T_TOTALSURPLUS(WORKID,VRTM_SORT,SRPLS_HR) VALUES(:NEW.IDS,:NEW.VRTMSORT,:NEW.AHR); 
                END IF;
             END IF; 
  ELSIF DELETING THEN
         UPDATE T_TOTALSURPLUS SET T_TOTALSURPLUS.SRPLS_HR=T_TOTALSURPLUS.SRPLS_HR-:OLD.AHR  WHERE T_TOTALSURPLUS.WORKID=:OLD.IDS AND T_TOTALSURPLUS.VRTM_SORT=:OLD.VRTMSORT;
  END IF;
     EXCEPTION
       WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END TUpdate_TSURPLUS;