CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Ajax
Ajax

Reputation: 150

PLSQL trigger to raise application error not working

I'm trying to create a trigger which raise error if the total number of row exceeds 10, The plsql code was successfully compiled but it's not generating any error. This is the plsql code:

SQL> CREATE OR REPLACE TRIGGER customer_count_check
  2  BEFORE INSERT OR UPDATE ON customer2
  3  FOR EACH ROW
  4  DECLARE
  5  count_customer NUMBER;
  6  max_customer NUMBER := 10;
  7  BEGIN
  8  SELECT COUNT(*) INTO count_customer FROM customer2 WHERE cusid = :new.cusid;
  9  IF count_customer >= max_customer THEN
 10  RAISE_APPLICATION_ERROR (-20000,'Customer Table capacity exceeded');
 11  END IF;
 12  END;
 13  /

Upvotes: 0

Views: 643

Answers (1)

hotfix
hotfix

Reputation: 3396

i would say your trigger does't work as you expect, because your select statement inside return always one row.

you filter on a primary key!

that should work

SQL> CREATE OR REPLACE TRIGGER customer_count_check
  2  BEFORE INSERT OR UPDATE ON customer2
  3  FOR EACH ROW
  4  DECLARE
  5  count_customer NUMBER;
  6  max_customer NUMBER := 10;
  7  BEGIN
  8  SELECT COUNT(*) INTO count_customer FROM customer2; --WHERE cusid = :new.cusid;
  9  IF count_customer >= max_customer THEN
 10  RAISE_APPLICATION_ERROR (-20000,'Customer Table capacity exceeded');
 11  END IF;
 12  END;
 13  /

Upvotes: 2

Related Questions