---------------------------------------------------------------------- -- Clean up from last try. ---------------------------------------------------------------------- drop trigger CheckEmployeeTrigger on Payroll; drop function CheckEmployeeFunc(); -- You will need to add drop statements for the second trigger and -- function here. drop table Payroll; ---------------------------------------------------------------------- -- Create new schema. ---------------------------------------------------------------------- create table Payroll ( Id integer, Name varchar(30), MId integer, -- MId is manager id. Salary integer, primary key (Id) ); -- This function enforces the constraint that no employee should earn -- more than their manager. If the employee's salary is greater than -- their manager's salary, adjust the employee's salary to equal that -- of their manager and raise a notice. If the employee has no manager, -- simply raise a notice. create function CheckEmployeeFunc() returns trigger as $$ declare begin end; $$ language 'plpgsql'; create trigger CheckEmployeeTrigger before insert or update on Payroll for each row execute procedure CheckEmployeeFunc(); -- This is where you will create the function and trigger for enforcing -- the constraint that no manager earns more than the sum of their -- employee's salaries. If a manager's salary exceeds this sum, adjust -- the salary to equal that of the sum and raise a notice. -- -- The associated row-level trigger must fire the function before -- insert or update events. ---------------------------------------------------------------------- -- Run a test suite. ---------------------------------------------------------------------- insert into payroll values (2, 'Tom', NULL, 75000); insert into payroll values (3, 'Phong', NULL, 75000); insert into payroll values (1, 'Jill', NULL, 100000); update payroll set MId = 1 where id = 2; update payroll set MId = 1 where id = 3; update payroll set salary = 150000 where id = 3; select * from payroll; update payroll set salary = 200000 where id = 1; select * from payroll;