09

  1. Trigger to update the total salary of a department when a new employee is hired
drop trigger if exists update_salary_on_insert;

delimiter \\
create trigger update_salary_on_insert
after insert on employee
for each row
begin
 if new.dno is not null then
     update deptSalary
        set totalSalary = totalSalary + new.salary
        where dnumber = new.dno;
    end if;
end \\
delimiter ;

select * from deptSalary;
insert into employee value(6, "Lucy", null, 90000, "1981-01-01", 1);
select * from deptSalary;
insert into employee values(7, "George", null, 45000, "1971-11-11", null);
select * from deptSalary;
  1. Trigger to update the total salary of a department when an employee tuple is modified (Adding/subtracting the difference is not safe, as there may be cases where the employee shifts to different department)
drop trigger if exists update_salary_on_update;

delimiter \\
create trigger update_salary_on_update
after update on employee
for each row
begin
 if old.dno is not null then
     update deptSalary
        set totalSalary = totalSalary - old.salary
        where dnumber = old.dno;
    end if;
    if new.dno is not null then
     update deptSalary
        set totalSalary = totalSalary + new.salary
        where dnumber = new.dno;
    end if;
end \\
delimiter ;

select * from deptSalary;
update employee set salary = 100000 where id = 6;
select * from deptSalary;
  1. Trigger to update the total salary of a department when an employee tuple is deleted
drop trigger if exists update_salary_on_delete;

delimiter \\
create trigger update_salary_on_delete
before delete on employee
for each row
begin
 if old.dno is not null then
     update deptSalary
        set totalSalary = totalSalary - old.salary
        where dnumber = old.dno;
    end if;
end \\
delimiter ;

select * from deptSalary;
delete from employee where id = 6;
select * from deptSalary;
delete from employee where id = 7;
select * from deptSalary;
2023-01-25

Comments