[SOLVED] Trying to create a trigger to check if there's more than 1 president in my database

Trying to create a trigger to check if there's more than 1 president in my database

I'm trying to find if there's more than 1 president in my database with a trigger and if yes, raise an error, I'm using hr, the table employees and I have to use the job_id to find it. Here's what my code looks like. Thanks!

CREATE OR REPLACE TRIGGER check_pres
BEFORE INSERT OR DELETE OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF ((employees.job_id = 'AD_PRES') > 1)
    THEN RAISE_APPLICATION_ERROR(-12345, 'More than one President in database.');
    END IF;
END;

Solution

You should use Statement Level Trigger instead of Row Level Trigger by removing FOR EACH ROW expression

CREATE OR REPLACE TRIGGER check_pres
  BEFORE INSERT OR DELETE OR UPDATE ON employees
DECLARE    
    v_cnt int;
BEGIN

    SELECT COUNT(*) INTO v_cnt FROM employees WHERE job_id = 'AD_PRES';

  IF ( v_cnt > 1 ) THEN
    RAISE_APPLICATION_ERROR(-20345, 'More than one President in database.');
  END IF;
END;

otherwise you'd get mutating error while getting the count value. Btw, the first argument value for RAISE_APPLICATION_ERROR should be between -20999 and -20000