----------------------------------------------------------------------
-- 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;
