Trigger

 1. Membuat Trigger Insert

CREATE TRIGGER Tot_employee
ON t_Employees
AFTER INSERT
AS
BEGIN
Declare @tot_emp int, @department int
Select @department = department from inserted
SELECT @tot_emp = isnull(Tot_employee,0)
from t_Departments
Where id = @department
Set @tot_emp = @tot_emp + 1
Update t_Departments
set tot_employee = @tot_emp
Where ID = @department
END

2. Membuat trigger delete

CREATE TRIGGER [dbo].[Del_Tot_employee]
ON [dbo].[t_Employees]
AFTER DELETE
AS
BEGIN
Declare @tot_emp int, @department int
Select @department = department from deleted
SELECT @tot_emp = isnull(Tot_employee,0)
from t_Departments
Where id = @department
Set @tot_emp = @tot_emp - 1
Update t_Departments
set tot_employee = @tot_emp
Where ID = @department
END

3. Membuat trigger update

CREATE TRIGGER [dbo].[Update_Tot_employee]
ON [dbo].[t_Employees]
AFTER UPDATE
AS
BEGIN
Declare @tot_emp_BARU int, @tot_emp_LAMA int,
@department_BARU int, @department_LAMA int,
@NIK VARCHAR(5)
Select @department_BARU = department from inserted
Select @NIK = NIK from inserted
Select @department_LAMA = department from deleted
--print @department_BARU
--print @department_LAMA
--print @NIK
if @department_BARU <> @department_LAMA
begin
SELECT @tot_emp_BARU = isnull(Tot_employee,0)
from t_Departments
Where id = @department_BARU
SELECT @tot_emp_LAMA = isnull(Tot_employee,0)
from t_Departments
Where id = @department_LAMA
Set @tot_emp_BARU = @tot_emp_BARU + 1
Set @tot_emp_LAMA = @tot_emp_LAMA - 1
Update t_Departments
set tot_employee = @tot_emp_BARU
Where ID = @department_BARU
Update t_Departments
set tot_employee = @tot_emp_LAMA
Where ID = @department_LAMA
end
else
begin
print 'tidak dijalankan'
end
END