can anyone help me out for this trigger.

CREATE or replace trigger check_limit_to_Y
AFTER INSERT OR UPDATE ON api_user for each row
WHEN EXISTS (SELECT '1' FROM profile b WHERE  NEW.mvno_limit!='Y' and b.mvno_id = NEW.mvno_id)
BEGIN
   raise_application_error (-20999,'MVNO LIMIT MUST BE SET Y FOR ANY REAL MVNO_ID');
END;

I got the error

Error report -
ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"
*Cause:    
*Action:

CodePudding user response：

This is from the Oracle site: https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS01374

WHEN (condition)

Specifies a SQL condition that the database evaluates for each row that the triggering statement affects. If the value of condition is TRUE for an affected row, then tps_body runs for that row; otherwise, tps_body does not run for that row. The triggering statement runs regardless of the value of condition.

The condition can contain correlation names (see referencing_clause ::=). In condition, do not put a colon (:) before the correlation name NEW, OLD, or PARENT (in this context, it is not a placeholder for a bind variable).

See Also: Oracle Database SQL Language Reference for information about SQL conditions

Restrictions on WHEN (condition)

• If you specify this clause, then you must also specify at least one of these timing points:
  • BEFORE EACH ROW
  • AFTER EACH ROW
  • INSTEAD OF EACH ROW
• The condition cannot include a subquery or a PL/SQL expression (for example, an invocation of a user-defined function).

Your code doesn't seem to satisfy the last two restrictions (also: the entire condition after the WHEN keyword must be enclosed in parenthesis).

But, you seem to need to use the 'compound trigger syntax' because you want to process both inserts and updates. Check the documentation carefully.

Update: Try this:

CREATE TRIGGER t
  BEFORE INSERT OR UPDATE ON api_user FOR EACH ROW
  
DECLARE  CNT integer; 
BEGIN
  SELECT count(*) into CNT FROM profile b where :NEW.mvno_limit!='Y' and b.mvno_id = :NEW.mvno_id;
  IF CNT > 0 THEN
      RAISE_APPLICATION_ERROR(-20999,'MVNO LIMIT MUST BE SET Y FOR ANY REAL MVNO_ID');
  END IF;
END;