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:
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
conditionisTRUEfor 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 ofcondition.The
conditioncan contain correlation names (seereferencing_clause ::=). Incondition, do not put a colon (:) before the correlation nameNEW,OLD, orPARENT(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 ROWAFTER EACH ROWINSTEAD 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;
SELECT RAISE(...do? RAISE is a statement afaik, not a function you can use in a SELECT clause.