Encountered the symbol "SELECT" when expecting one of the following:-CodePudding

i need help from you guys. I am using oracle apex for university, and my task is to create triggers. I'm struggling a lot creating them, I don't even know what is wrong with this trigger. Can someone help me?

I get error

Encountered the symbol "SELECT" when expecting one of the following: ( -   case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification> <an alternat
Error at line 3: PLS-00049: bad bind variable 'NEW'
Error at line 3: PLS-00103: Encountered the symbol "" when expecting one of the following: . ( * @ % & = -   ; < / > at for in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between group having intersect minus order start union where connect || indicator multiset member submultiset
Error at line 3: PLS-00049: bad bind variable 'CONTRACT' Error at line 3: PLS-00049: bad bind variable 'CONTRACT'   -

CREATE OR REPLACE TRIGGER restrict_orders
    BEFORE INSERT ON orders
        FOR EACH ROW
        BEGIN
        DECLARE countOrders number;
        set countOrders := SELECT count(contract) FROM orders WHERE :new:contract := old:contract AND (:old.status := 'PREPARING' OR :old.status := 'IN PROGRESS');
            IF (countOrders :> 3)
            THEN
                RAISE_APPLICATION_ERROR (-20202, 'You have too much active orders.');
            END IF;
    END;

CodePudding user response：

Quite a few errors; see if this helps.

Sample table:

SQL> select * From orders;

  CONTRACT STATUS
---------- ---------
         1 PREPARING

Trigger:

SQL> CREATE OR REPLACE TRIGGER restrict_orders
  2    BEFORE INSERT ON orders
  3    FOR EACH ROW
  4  DECLARE
  5    countOrders number;
  6  BEGIN
  7    SELECT count(contract)
  8      INTO countOrders
  9      FROM orders
 10      WHERE contract = :new.contract
 11        AND status IN ('PREPARING', 'IN PROGRESS');
 12
 13    IF countOrders > 3 THEN
 14       RAISE_APPLICATION_ERROR (-20202, 'You have too many active orders.');
 15    END IF;
 16  END;
 17  /

Trigger created.

Testing:

SQL> insert into orders (contract, status) values (1, 'PREPARING');

1 row created.

SQL> insert into orders (contract, status) values (1, 'PREPARING');

1 row created.

SQL> insert into orders (contract, status) values (1, 'PREPARING');

1 row created.

SQL> insert into orders (contract, status) values (1, 'PREPARING');
insert into orders (contract, status) values (1, 'PREPARING')
*
ERROR at line 1:
ORA-20202: You have too many active orders.
ORA-06512: at "SCOTT.RESTRICT_ORDERS", line 11
ORA-04088: error during execution of trigger 'SCOTT.RESTRICT_ORDERS'

Kind of works. However, note that it'll fail if you attempt to insert more than a single row at a time because table will be mutating (so you'd have to take another, more complex approach), but - if you'll insert just one row at a time, you're good:

SQL> insert into orders (contract, status)
  2  select 1, 'PREPARING'   from dual union all
  3  select 2, 'IN PROGRESS' from dual;
insert into orders (contract, status)
            *
ERROR at line 1:
ORA-04091: table SCOTT.ORDERS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.RESTRICT_ORDERS", line 4
ORA-04088: error during execution of trigger 'SCOTT.RESTRICT_ORDERS'


SQL>

CodePudding user response：

You can't assign countOrders that way.

Try:

CREATE OR REPLACE TRIGGER restrict_orders
    BEFORE INSERT ON orders
        FOR EACH ROW
        BEGIN
        DECLARE countOrders number;
        SELECT count(contract) INTO countOrders FROM orders WHERE :new:contract := old:contract AND (:old.status := 'PREPARING' OR :old.status := 'IN PROGRESS');
            IF (countOrders :> 3)
            THEN
                RAISE_APPLICATION_ERROR (-20202, 'You have too much active orders.');
            END IF;
    END;

CodePudding user response：

You need to correct your syntax which should be -

CREATE OR REPLACE TRIGGER restrict_orders
BEFORE INSERT ON orders
FOR EACH ROW
DECLARE countOrders number;
BEGIN
     SELECT count(contract)
       INTO countOrders
       FROM orders
      WHERE :new.contract = :old.contract 
        AND :old.status IN ('PREPARING', 'IN PROGRESS');

     IF (countOrders >= 3) THEN
        RAISE_APPLICATION_ERROR (-20202, 'You have too much active orders.');
     END IF;
END;