oracle-databaseplsqldatabase-triggerora-00936

In PL/SQL program I'm getting Missing expression error as follows


CREATE OR REPLACE TRIGGER sales_trig1 
    before insert on Inventory1 
    for each row
DECLARE
    q Inventory1.qty%type; 
    id Inventory1.item_id%type;
    si Sales.sitem_id%type; 
    tid Sales.trans_id%type; 
    td Sales.trans_dt%type; 
    tq Sales.trans_qty%type;
BEGIN
    tid:='t_id111';  
    si:='&si';  
    tq:=&tq;
    select item_id,qty 
    into id,q 
    from Inventory1 
    where id=si; 
    IF tq<=q THEN 
       insert into Sales 
       values(tid,si,date,tq); 
       q:=q-tq; 
    END IF;    
EXCEPTION 
    WHEN no_data_found THEN 
         dbms_output.put_line('EXCEPTION: Invalid item_id');

END;

Error is as follows

Errors for TRIGGER SALES_TRIG1:
-
LINE/COL ERROR
-------- ----------------------------------------------------------------- 
17/1     PL/SQL: SQL Statement ignored
17/33   PL/SQL: ORA-00936: missing expression

Solution

  • If you want to get and change values from newly inserted rows, use :NEW.<column_name> to address new values.

    Here's how it could apply to your case:

    CREATE OR REPLACE TRIGGER sales_trig1 
      before insert on Inventory1 
        for each row
    DECLARE
      q Inventory1.qty%type; 
      id Inventory1.item_id%type;
      si Sales.sitem_id%type; 
      tid Sales.trans_id%type; 
      td Sales.trans_dt%type; 
      tq Sales.trans_qty%type;
    BEGIN
      tid :='t_id111';  
      si  := :NEW.sytem_id;  
      tq  := :NEW.trans_dt;
      select item_id, qty into id, q from Inventory1 where id = si; 
      IF tq <= q THEN 
        -- insert in another table
        insert into Sales values(tid, si, sysdate, tq); 
        -- here change inserted value
        :NEW.qty := q - tq; 
      END IF;    
    EXCEPTION 
      WHEN no_data_found THEN 
         dbms_output.put_line('EXCEPTION: Invalid item_id');
    
    END;
    /