Trigger created with compilation errors. PL/SQL-CodePudding

I've been searching for a while how to resolve this issue and couldn't find anything helpful. Why this trigger is created with compilation errors? The warning im getting

Warning: Trigger created with compilation errors.

CREATE OR REPLACE TRIGGER peope_in_dept
AFTER INSERT OR DELETE OR UPDATE on emp
FOR EACH ROW
BEGIN
     VARIABLE v NUMBER;
     EXECUTE :v := count_people_in_dept(:new.deptno)
     IF :v <= 0 or :v >= 10 THEN
        raise_application_error(-20000,'Nieodpowiednia liczba pracownikow');
     END IF;
END;
/

CodePudding user response：

Syntax errors. Should be

SQL> CREATE OR REPLACE TRIGGER peope_in_dept
  2     AFTER INSERT OR DELETE OR UPDATE
  3     ON emp
  4     FOR EACH ROW
  5  DECLARE
  6     v  NUMBER;
  7  BEGIN
  8     v := count_people_in_dept (:new.deptno);
  9
 10     IF    v <= 0
 11        OR v >= 10
 12     THEN
 13        raise_application_error (-20000, 'Nieodpowiednia liczba pracownikow');
 14     END IF;
 15  END;
 16  /

Trigger created.

SQL>

However, I suspect it won't work if count_people_in_dept function counts employees in emp table. Why? Because table is mutating:

SQL> UPDATE emp SET comm = 1 WHERE deptno = 10;
UPDATE emp SET comm = 1 WHERE deptno = 10
       *
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.COUNT_PEOPLE_IN_DEPT", line 6
ORA-06512: at "SCOTT.PEOPE_IN_DEPT", line 4
ORA-04088: error during execution of trigger 'SCOTT.PEOPE_IN_DEPT'


SQL>

Now, it depends on what you really are doing in that function. One way out is to set the function to be an autonomous transaction; another one is to use compound trigger; the third option is to use custom type and a package. Or, modify the process altogether (i.e. avoid the trigger).

CodePudding user response：

Why this trigger is created with compilation errors?

Because you are trying to create a SQL/Plus global bind variable inside a PL/SQL procedure and then using EXECUTE rather than just calling the function (and you missed a ; statement terminator).

The correct syntax would be:

FOR EACH ROW
DECLARE
  v NUMBER;
BEGIN
  v := count_people_in_dept(:new.deptno);
  IF v <= 0 or v >= 10 THEN

Then it would compile. However, it still won't work as the table would be mutating as the trigger is running.

Instead, you can use a statement trigger rather than a row trigger:

CREATE OR REPLACE TRIGGER people_in_dept
AFTER INSERT OR DELETE OR UPDATE on emp
DECLARE
  v_count PLS_INTEGER;
BEGIN
  SELECT 1
  INTO   v_count
  FROM   dept d
         LEFT OUTER JOIN emp e
         ON (e.deptno = d.deptno)
  GROUP BY d.deptno
  HAVING COUNT(e.deptno) NOT BETWEEN 1 AND 9
  FETCH FIRST ROW ONLY;
  
  raise_application_error(-20000,'Nieodpowiednia liczba pracownikow');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Do nothing.
    NULL;
END;
/

db<>fiddle here

You could also write it as a compound trigger that aggregates the changes in deptno and then only checks the changed values using your function; however, that is a lot more complicated.