资金表实现过程方案二:资金表与冻结表分离(一)

1. 分别在资金表与冻结表建立两个触发器

 

CREATE OR REPLACE TRIGGER TRG_TAB_TRADESETTLEMENT_HIS29
   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.SERIALNUMBER,
                                   :NEW.VALID,
                                   :NEW.CATEGORY_ID,
                                   :NEW.MEMBER_ID,
                                   :NEW.MERCHANT_ID,
                                   :NEW.ORDER_ID);
      WHEN UPDATING
      THEN
         raise_application_error (-20001,
                                  'Settlement data and banned modification!');
      WHEN DELETING
      THEN
         raise_application_error (-20001,
                                  'Settlement data, forbid to delete!');
   END CASE;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('PLEASE OPERATION AGAIN,THANKS!');
      RAISE_APPLICATION_ERROR (
         -20001,
         'TRG_TAB_TRADESETTLEMENT HAVE ERROR POSSIBLY,PLEASE CHECK IT!'
      );
END TRG_TAB_TRADESETTLEMENT_HIS29;
/

 

 

CREATE OR REPLACE TRIGGER TRG_TAB_FROZENCAPITAL_HIS29
   BEFORE INSERT OR DELETE OR UPDATE
   ON TAB_FROZENCAPITAL
   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
         SP_FROZEN_FOR_INSERTING (:NEW.MONEY,
                                  :NEW.VALID,
                                  :NEW.MEMBER_ID,
                                  :NEW.MERCHANT_ID);
      WHEN UPDATING
      THEN
         DBMS_OUTPUT.PUT_LINE ('OPERATION SUCCESS,THANKS!');
      --         raise_application_error (-20001,
      --                                  'Frozen data and banned modification!');
      WHEN DELETING
      THEN
         raise_application_error (-20001, 'Frozen data, forbid to delete!');
   END CASE;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('PLEASE OPERATION AGAIN,THANKS!');
      RAISE_APPLICATION_ERROR (
         -20001,
         'TRG_TAB_FROZENCAPITAL HAVE ERROR POSSIBLY,PLEASE CHECK IT!'
      );
END TRG_TAB_FROZENCAPITAL_HIS29;
/

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-745868/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-745868/