1. 触发器调用存储过程

a. 向资金表插入数据前查询出当前交易前余额

b. 分别调用三个存储过程实现该表的insert/update/delete 三种操作的触发

 

CREATE OR REPLACE TRIGGER TRG_TAB_TRADESETTLEMENT_HIS28
   BEFORE INSERT OR DELETE OR UPDATE
   ON TAB_TRADESETTLEMENT
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   T_PREMONEY      FLOAT (126);
   T_MEMBER_ID     NUMBER (19);
   T_MERCHANT_ID   NUMBER (19);
   T_BALANCE       NUMBER (19);
   T_EXIST         NUMBER (19);
BEGIN
   CASE
      WHEN INSERTING
      THEN
         T_MEMBER_ID := :NEW.MEMBER_ID;
         T_MERCHANT_ID := :NEW.MERCHANT_ID;

         IF T_MEMBER_ID IS NULL
         THEN
            SELECT   COUNT (MERCHANT_ID)
              INTO   t_exist
              FROM   TAB_MERCHANTFUND
             WHERE   MERCHANT_ID = T_MERCHANT_ID;

            IF t_exist > 0
            THEN
               SELECT   balance
                 INTO   t_balance
                 FROM   TAB_MERCHANTFUND
                WHERE   MERCHANT_ID = T_MERCHANT_ID;
            ELSE
               t_balance := 0;
            END IF;
         ELSIF T_MERCHANT_ID IS NULL
         THEN
            SELECT   COUNT (MEMBER_ID)
              INTO   t_exist
              FROM   TAB_MEMBERFUND
             WHERE   MEMBER_ID = T_MEMBER_ID;

            IF t_exist > 0
            THEN
               SELECT   balance
                 INTO   t_balance
                 FROM   TAB_MEMBERFUND
                WHERE   MEMBER_ID = T_MEMBER_ID;
            ELSE
               t_balance := 0;
            END IF;
         END IF;

         :NEW.PREMONEY := T_BALANCE;

         SP_BALANCE_FOR_INSERTING (:NEW.ID,
                                   :NEW.COLUMNSIGN,
                                   :NEW.CREATEDATE,
                                   :NEW.LASTDATE,
                                   :NEW.MONEY,
                                   :NEW.VALID,
                                   :NEW.CATEGORY_ID,
                                   :NEW.MEMBER_ID,
                                   :NEW.MERCHANT_ID,
                                   :NEW.ORDER_ID);
      WHEN UPDATING
      THEN
         SP_BALANCE_FOR_UPDATING (:NEW.MONEY,
                                  :OLD.CATEGORY_ID,
                                  :NEW.MEMBER_ID,
                                  :NEW.MERCHANT_ID,
                                  :OLD.MONEY,
                                  :NEW.VALID,
                                  :OLD.VALID);
      WHEN DELETING
      THEN
         SP_BALANCE_FOR_DELETING (:OLD.MONEY,
                                  :OLD.CATEGORY_ID,
                                  :OLD.MEMBER_ID,
                                  :OLD.MERCHANT_ID,
                                  :OLD.VALID);
   END CASE;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('PLEASE OPERATION AGAIN,THANKS!');
      RAISE_APPLICATION_ERROR (
         -20001,
         'TRG_TAB_TRADESETTLEMENT_HIS HAVE ERROR POSSIBLY,PLEASE CHECK IT!'
      );
END TRG_TAB_TRADESETTLEMENT_HIS28;
/