Trigger that changes value in another table /plsql-CodePudding

I've created two tables: Employees and Departments

CREATE TABLE EMP
( emp_id number(3) PRIMARY KEY,
  dept_id Number(3) NOT NULL,
  emp_name Varchar2(50) NOT NULL,
  address Varchar2(100),
  phone Varchar2(20) NOT NULL,
  salary Number(8,2) NOT NULL,
CONSTRAINT fk_DEPT FOREIGN KEY (dept_id) REFERENCES DEPT(DEPT_ID));

CREATE TABLE DEPT
( dept_id number(3) PRIMARY KEY,
  dept_name varchar2(50) NOT NULL,
  emp_cnt Number(3) NOT NULL)

I need to create trigger that changes value in DEPT.emp_cnt after inserting or deleting data in EMP table.

Here is my attempt

create or replace trigger add_emp_to_the_dep
after insert or delete on EMP
for each row 
    begin
        update DEPT
        set emp_cnt = :new.emp_id
        where DEPT.dept_id = :new.dept_id;
        if INSERTING then 
            emp_cnt  = 1;
        else DELETING then
            emp_cnt -= 1;
        end if;
end;

CodePudding user response：

Wrong syntax; there's no such thing as emp_cnt = 1; in Oracle's PL/SQL.

Try something like this instead:

create or replace trigger add_emp_to_the_dep
  after insert or delete on emp
  for each row
begin
  if inserting then
     update dept set
       emp_cnt = emp_cnt   1
       where dept_id = :new.dept_id;
  elsif deleting then
     update dept set
        emp_cnt = emp_cnt - 1
        where dept_id = :old.dept_id;
  end if;
end;
/

CodePudding user response：

You can use a compound trigger to collate the changes and make the minimum number of updates:

CREATE TRIGGER add_emp_to_the_dep
FOR INSERT OR UPDATE OR DELETE ON emp
COMPOUND TRIGGER
  TYPE ids_type IS TABLE OF EMP.DEPT_ID%TYPE;
  TYPE cnt_type IS TABLE OF PLS_INTEGER;
  TYPE idx_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  ids  ids_type := ids_type();
  cnts cnt_type := cnt_type();
  idxs idx_type := idx_type();
  
  PROCEDURE modify_dept_cnt (
    id  EMP.DEPT_ID%TYPE,
    cnt PLS_INTEGER
  )
  IS
  BEGIN
    IF id IS NULL THEN
      RETURN;
    END IF;
    IF NOT idxs.EXISTS(id) THEN
      ids.EXTEND;
      cnts.EXTEND;
      ids(ids.COUNT) := id;
      cnts(cnts.COUNT) := cnt;
      idxs(id) := ids.COUNT;
    ELSE
      cnts(idxs(id)) := cnts(idxs(id))   cnt;
    END IF;
  END modify_dept_cnt;
AFTER EACH ROW
  IS
  BEGIN
    modify_dept_cnt(:NEW.DEPT_ID, 1);
    modify_dept_cnt(:OLD.DEPT_ID, -1);
  END AFTER EACH ROW;
AFTER STATEMENT
  IS
  BEGIN
    FORALL i IN 1 .. ids.count
      UPDATE dept
      SET   emp_cnt = emp_cnt   cnts(i)
      WHERE dept_id = ids(i);
  END AFTER STATEMENT;
END;
/

Then, if you do:

INSERT INTO emp (emp_id, dept_id, emp_name, phone, salary)
SELECT 1, 1, 'Alice', '0', 100 FROM DUAL UNION ALL
SELECT 2, 1, 'Betty', '1', 100 FROM DUAL UNION ALL
SELECT 3, 2, 'Carol', '2', 100 FROM DUAL UNION ALL
SELECT 4, 1, 'Debra', '3', 100 FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', '4', 100 FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', '5', 100 FROM DUAL;

It will collate all the changes and UPDATE the DEPT table only 3 times, as employees for 3 unique DEPT_ID are added, rather than performing 6 updates, one for each inserted row.

db<>fiddle here