CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqltriggers
Darshak Chavda
Darshak Chavda

Reputation: 33

Table not updating when trigger is fired

I have 2 tables:

Employees(Employee_ID, First_name, Last_name, Email, Hire_date, Department_ID)

Departments(Department_ID, Department_name, Total_employees).

I have created trigger that fires after inserting an employee to employees table. It increases the total_employees column by 1 for :new inserted Department_ID.

Trigger:

create or replace TRIGGER emp_count_department
AFTER INSERT OR DELETE ON EMPLOYEES
for each row
DECLARE
    counter NUMBER;
BEGIN
    if inserting then
        select total_employees into counter
        from departments
        where department_id = :new.department_id;

        update departments
        set total_employees = counter+1
        where department_id = :new.department_id;

    elsif deleting then
        select total_employees into counter
        from departments
        where department_id = :old.department_id;

        update departments
        set total_employees = counter-1
        where department_id = :old.department_id;
    end if;
END;

When I try to insert an employee, it is inserted but it doesn't increase total_employees by 1 in departments table. When I try to insert an employee with a department_ID that doesn't exist in departments table, it gives me error. So the trigger is working but not increasing the total numbers by 1.

Upvotes: 0

Views: 81

Answers (3)

Del
Del

Reputation: 1599

I think you are missing an important use case, what if the employees table is updated and the dept_id changes. To expand upon the answer @pmdba gave, I think the following code would work best.

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.department_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.department_id = :old.dept_id;
  END IF;
END;
/

This takes care of UPDATE statements now. And also takes care of the first INSERT for a given department (If you didn't initialize all departments.total_employee to 0 when you created them).

I also created this DBFiddle demonstrating the functionality (And why you need the NVL function or to default your TOTAL_EMPLOYEES column to 0) (Link)

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

I did simple testing of your code in Oracle 11g and it seems to work. Please check if you missed in adding information.

 create table emp1(emp_id number ,dept_id number);

 create table dept1 (dept_id number ,total_emp number);

    CREATE OR replace TRIGGER emp_count_dept
      AFTER INSERT OR DELETE ON emp1
      FOR EACH ROW
    DECLARE
        counter NUMBER;
    BEGIN
        IF inserting THEN
          UPDATE dept1
          SET    total_emp = total_emp + 1
          WHERE  dept_id = :new.dept_id;
        ELSIF deleting THEN
          UPDATE dept1
          SET    total_emp = total_emp - 1
          WHERE  dept_id = :old.dept_id;
        END IF;
    END; 

    insert into dept1 values(10,10);

    insert into emp1 values(10,10);

    select * from dept1;

    DEPT_ID TOTAL_EMP
        10  11

Upvotes: 0

pmdba
pmdba

Reputation: 7033

Try this:

create or replace TRIGGER emp_count_department
AFTER INSERT OR DELETE ON EMPLOYEES
for each row
BEGIN
    if inserting then
        update departments
        set total_employees = total_employees+1
        where department_id = :new.department_id;

    elsif deleting then
        update departments
        set total_employees = total_employees-1
        where department_id = :old.department_id;
    end if;
END;

Upvotes: 1

Related Questions