Trigger Logic
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Trigger Logic

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Really need help with this one:
    SQL> desc credit_limit_history;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUSTOMER_ID NOT NULL VARCHAR2(15)
    CREDIT_LIMIT NOT NULL NUMBER(7,2)
    CHANGED_BY NOT NULL VARCHAR2(25)
    CHANGED_DATE NOT NULL DATE
    CREDIT_LIMIT_ID NOT NULL VARCHAR2(10)
    TYPE VARCHAR2(10)

    SQL> desc credit_interface;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUSTOMER_ID VARCHAR2(15)
    FINANCE_CREDIT_LIMIT NUMBER(7,2)
    CASH_CREDIT_LIMIT NUMBER(7,2)
    SUBSIDY_CREDIT_LIMIT NUMBER(7,2)

    Note: In credit_limit_history table, if column credit_limit is for example $100,
    then type will show if it is FINANCE, CASH, or SUBSIDY.
    So, we are store in that column FINANCE, CASH and SUBSIDY for now.
    In the interface table, FINANCE_CREDIT_LIMIT, CASH_CREDIT_LIMIT and SUBSIDY_CREDIT_LIMIT are used to capture the values of CREDIT_LIMIT from credit_limit_history table.

    The logic of my trigger is as follows:
    SQL> CREATE OR REPLACE TRIGGER credit_trig_iu
    2 AFTER insert or update ON CREDIT_LIMIT_HISTORY
    3 for each row
    4 BEGIN
    5 IF (type = 'FINANCE') THEN
    6 INSERT INTO credit_interface(finance_credit_limit)
    7 VALUES(:new.credit_limit);
    8 ELSE
    9 IF (type = 'CASH') THEN
    10 INSERT INTO credit_interface(cash_credit_limit)
    11 VALUES(:new.credit_limit);
    12 ELSE
    13 IF (type = 'SUBSIDY') THEN
    14 INSERT INTO credit_interface(subsidy_credit_limit)
    15 VALUES(:new.credit_limit);
    16 END IF;
    17 END;
    18 /

    Warning: Trigger created with compilation errors.

    SQL> show errors;
    Errors for TRIGGER CREDIT_TRIG_IU:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/4 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    if


  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    CREATE OR REPLACE TRIGGER credit_trig_iu
    AFTER insert or update ON CREDIT_LIMIT_HISTORY
    for each row
    BEGIN
    IF (:new.type = 'FINANCE') THEN
    INSERT INTO credit_interface(finance_credit_limit) VALUES(:new.credit_limit);
    ELSIF (:new.type = 'CASH') THEN
    INSERT INTO credit_interface(cash_credit_limit) VALUES(:new.credit_limit);
    ELSIF (:new.type = 'SUBSIDY') THEN
    INSERT INTO credit_interface(subsidy_credit_limit) VALUES(:new.credit_limit);
    ELSE
    -- error in type variable
    -- for example : raise_application_error(...);
    null;
    END IF;
    -- had better add exception block
    --
    --EXCEPTION when ...
    END;
    /

  3. #3
    Join Date
    Aug 2001
    Posts
    184
    its ELSIF not ELSE IF. if u are starting a new IF block, it shood be ended with END IF.
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  4. #4
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Thank you so much Shestakov
    The trigger created. I will be testing it by next week with real values. Thanks again.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width