Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement