Laba_5_BD_triggers

--task 1
create or alter trigger CompanyDel
on Company
instead of delete
as
delete Department
from deleted
where deleted.Company_id = Department.Company_id
delete Department_head
from deleted
join Department on Department.Company_id=deleted.Company_id
join Department_head on Department_head.Dept_head_id = Department.Dept_head_id
delete company from deleted
where Company.Company_id = deleted.Company_id


--task 2
create or alter trigger UniqueWeek
on Evaluation
instead of insert
as
declare @Id int, @LDate date
begin
select @Id = inserted.Cooperator_id
from inserted
select @LDate = Evaluation.Date
from Evaluation
where Evaluation.Cooperator_id = @Id

if(select DATEDIFF(week, @Ldate, inserted.Date) from inserted) <> 0
    insert into Evaluation
    select inserted.Cooperator_id, inserted.Status, inserted.Date
    from inserted
else
    print 'Не прошло недели'
end

insert into Evaluation
values('2',5,'15/10/2023')

--task 3
create or alter trigger CooperCheck
on Cooperator
after insert
as
begin
 DECLARE @DepID INT
 DECLARE @CoopCount INT


 SELECT @DepID = inserted.Dept_id from inserted
 SELECT @CoopCount = Number_cooperator FROM Department_cooperator WHERE Department_cooperator.Dept_id = @DepID

  IF (@CoopCount > 4)
 BEGIN
  ROLLBACK TRANSACTION
 END
 ELSE
 BEGIN
  UPDATE Department_cooperator SET Number_cooperator = Number_cooperator + 1 WHERE Department_cooperator.Dept_id = @DepID
 END
END

insert into Cooperator
values('Тестов','тест','15/10/2000','Уфа',100000,'Программист' ,'15/10/2023',2,'893422423','15465457')


--zashita
/*
триггер который будет срабатывать при удалении департамента(отдела), если этот департамент последний то удаляем и компанию
*/

create trigger DeptDel
on Department
after delete
as
declare @Compid int
begin
    select @Compid = deleted.Company_id from deleted

    if not exists (select Department.Company_id from Department where Department.Company_id = @Compid)
    begin
        delete from Company where Company_id = @Compid
    end
end

delete from Department where Dept_id = 17
delete from Department where Dept_id = 18