I am having PLS-00103: Encountered the symbol

Question

i am having

PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge error

and i have searched everywhere but couldnt find how to solve

CREATE TABLE UPAYMENT AS SELECT * FROM PAYMENT ;


DROP TRIGGER A;
CREATE OR REPLACE TRIGGER A
    after insert ON Upayment
    FOR EACH ROW
    DECLARE
    ERROR_ EXCEPTION ;
    h varchar2(20);
    NOFOUND EXCEPTION ;
    CURSOR Y3 IS 
    SELECT PAYMENT_DATE FROM UPAYMENT ;
BEGIN
OPEN Y3;
loop fetch Y3 into h;
IF Y3%NOTFOUND THEN RAISE NOTFOUND;
    IF (:NEW.PAYMENT_DATE >= '01-JAN-22'and INSERTING)
    THEN
        :NEW.AMOUNT := :NEW.AMOUNT * 1.5;
       ELSE
        END LOOP;
    END IF;
    CLOSE Y3;
 END IF;
    exception when ERROR_
then DBMS_OUTPUT.PUT_LINE('ERROR');
END;

Answer 1

Quite a few errors.

• trigger fires on insert; you don't have to check whether you're inserting (besides, you can't do that as you did)
• don't compare dates to strings; use date literal or TO_DATE function with appropriate format mask
• you can't modify :new values in an after trigger; must be before
• you can't (actually, you shouldn't) end loop in the middle of IF
• what do you need the loop for, anyway?

When fixed, trigger looks like this and kind of works:

SQL> create or replace trigger a
  2    before insert on upayment
  3    for each row
  4  declare
  5    error_ exception ;
  6    h varchar2(20);
  7    nofound exception ;
  8    cursor y3 is
  9      select payment_date from upayment ;
 10  begin
 11    open y3;
 12    loop
 13      fetch y3 into h;
 14
 15      if y3%notfound then
 16         raise nofound;
 17      else
 18         if :new.payment_date >= date '2022-01-01' then
 19            :new.amount := :new.amount * 1.5;
 20         end if;
 21      end if;
 22    end loop;
 23    close y3;
 24
 25  exception when nofound then
 26    dbms_output.put_line('ERROR');
 27  end;
 28  /

Trigger created.

---

SQL> desc upayment
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PAYMENT_DATE                                       DATE
 AMOUNT                                             NUMBER

SQL> insert into upayment (payment_date, amount) values (sysdate, 100);
ERROR

1 row created.

SQL>

---

Though, I guess that you could simplify it (remove cursor, loop, user-defined exception, ... what not) to

SQL> create or replace trigger a
  2    before insert on upayment
  3    for each row
  4  begin
  5    if :new.payment_date >= date '2022-01-01' then
  6       :new.amount := :new.amount * 1.5;
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into upayment (payment_date, amount) values (sysdate, 500);

1 row created.

SQL> select * from upayment;

PAYMENT_DATE            AMOUNT
------------------- ----------
19.01.2022 22:28:03        150
19.01.2022 22:32:57        750

SQL>