Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE departments
(
  dept_id INTEGER PRIMARY KEY,
  dept_name VARCHAR2(50) NOT NULL,
  total_employees INTEGER
);
CREATE TABLE employees
(
  emp_id INTEGER PRIMARY KEY,
  emp_name VARCHAR2(50) NOT NULL,
  dept_id REFERENCES departments(dept_id)
);
CREATE OR REPLACE TRIGGER emp_count_dept
AFTER INSERT OR UPDATE OF DEPT_ID OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF :new.dept_id IS NOT NULL THEN
    UPDATE departments d
    SET d.total_employees = total_employees + 1 --NVL(d.total_employees, 0)+1
    WHERE d.dept_id = :new.dept_id;
  END IF;

  IF :old.dept_id IS NOT NULL THEN
    UPDATE departments d
    SET d.total_employees = d.total_employees - 1
    WHERE d.dept_id = :old.dept_id;
  END IF;
END;
/
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'HR');
1 rows affected

INSERT INTO departments (dept_id, dept_name) VALUES (2, 'Sales');
1 rows affected

INSERT INTO employees VALUES (1, 'John Smith', 1);
1 rows affected

SELECT *
FROM departments;


DEPT_ID
DEPT_NAME
TOTAL_EMPLOYEES


1
HR
null

2
Sales
null


DELETE FROM employees WHERE emp_id = 1;
1 rows affected

CREATE OR REPLACE TRIGGER emp_count_dept
AFTER INSERT OR UPDATE OF DEPT_ID OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF :new.dept_id IS NOT NULL THEN
    UPDATE departments d
    SET d.total_employees = NVL(d.total_employees, 0)+1
    WHERE d.dept_id = :new.dept_id;
  END IF;

  IF :old.dept_id IS NOT NULL THEN
    UPDATE departments d
    SET d.total_employees = d.total_employees - 1
    WHERE d.dept_id = :old.dept_id;
  END IF;
END;
/
INSERT INTO employees VALUES (1, 'John Smith', 1);
1 rows affected

SELECT *
FROM departments;


DEPT_ID
DEPT_NAME
TOTAL_EMPLOYEES


1
HR
1

2
Sales
null


UPDATE employees SET dept_id = 2 WHERE emp_id = 1;
1 rows affected

SELECT *
FROM departments;


DEPT_ID
DEPT_NAME
TOTAL_EMPLOYEES


1
HR
0

2
Sales
1


DELETE FROM employees WHERE emp_id = 1;
1 rows affected

SELECT *
FROM departments;


DEPT_ID
DEPT_NAME
TOTAL_EMPLOYEES


1
HR
0

2
Sales
0