Trigger Logic

[akwete]

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

[Shestakov]

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;
/

[ocpwannabe]

its ELSIF not ELSE IF. if u are starting a new IF block, it shood be ended with END IF.

[akwete]

Thank you so much Shestakov
The trigger created. I will be testing it by next week with real values. Thanks again.