سوال ۱۱۴ کنکور ارشد مهندسی کامپیوتر ۱۴۰۳

ALTER TABLE Employees ADD CONSTRAINT SalaryBudgetCheck CHECK
(SUM(Salary) <= (SELECT Budget FROM Departments WHERE DepartmentID
= Employees.DepartmentID))

CREATE ASSERTION SalaryBudgetCheck
CHECK(NOT EXISTS (SELECT DepartmentID FROM Employees
E WHERE SUM(E.Salary) > (SELECT D.Budget
FROM Departments D WHERE D.DepartmentID = E.DepartmentID)))

CREATE ASSERTION SalaryBudgetCheck AS CHECK
(SELECT SUM(E.Salary) FROM Employees E GROUP BY E.DepartmentID
= SELECT D.Budget FROM Departments D
WHERE D.DepartmentID = E.DepartmentID)

CREATE TRIGGER SalaryBudgetCheck BEFORE INSERT OR UPDATE ON
Employees FOR EACH ROW EXECUTE RPOCEDURE CheckSalaryBudget()