Btukfyl

I have two tables.

CREATE TABLE `departments` (

department_id INT(2) NOT NULL AUTO_INCREMENT,

department_name VARCHAR(30) NOT NULL,

total_employees INT(4),

PRIMARY KEY (department_id),

UNIQUE (department_name));



CREATE TABLE `employees` (

employee_id INT(4) NOT NULL AUTO_INCREMENT,

employee_email VARCHAR(30) NOT NULL,

employee_first_name VARCHAR(30) NOT NULL,

employee_last_name VARCHAR(30) NOT NULL,

department_name VARCHAR(30) NOT NULL,

PRIMARY KEY (employee_id),

UNIQUE (employee_email),

FOREIGN KEY (department_name)

    REFERENCES departments (department_name)

    ON DELETE CASCADE);

Now, suppose I've a department which is HRM. 2 employees have HRM department. So, I want the value of total_employees of departments table 2 through a trigger. Would be great if I could get some suggestions of yours.

In an insert trigger there is no "old"...because you're inserting a row, not updating one. There is no previous version of the row, because it never existed before. You can just use the department_id in the newly created row using new instead. And you don't need the if because there's nothing to compare to:

delimiter $$ 

create trigger employee_insertion_in_individual_department

  after insert on employees for each row begin



    update departments set total_employees=total_employees+1 

    where department_id=new.department_id; 



end$$ delimiter ;

See also How to get inserted value for trigger

P.S. It might be safer to update this by using a count of the actual rows, rather than a simple increment. I'm worried it might go askew if lots of rows are changed at almost the same time. It may be worth testing whether this causes issues or not.

P.P.S. I assume you also plan to add UPDATE and DELETE triggers for the same purpose, if you haven't already.

You seem to want an update trigger, not an insert trigger. The insert trigger would be:

delimiter $$ 



create trigger employee_insertion_in_individual_department

after insert on employees for each row

begin

    update departments set 

        total_employees = total_employees + 1 

        where department_id = new.department_id;

    end if;

end$$



delimiter ;

The update trigger would be more like:

delimiter $$ 

create trigger employee_insertion_in_individual_department

after insert on employees for each row

begin

    if not (old.employee_department <=> old.employee_department) then

        update departments set 

            total_employees = total_employees + (case when department_id = old_department_id then -1 else 1 end)

            where department_id in (old.department_id, new.department_id);

    end if;

end$$



delimiter ;