----------------------------------------------------------------------
-- Clean up from last try.
----------------------------------------------------------------------

drop trigger VerifySalary on Employee;
drop function Verify();
drop table Employee;


----------------------------------------------------------------------
-- Create new schema.
----------------------------------------------------------------------

create table Employee
(
   Name   varchar(30),
   Salary integer
);

-- Ensure that no salary is negative and that the sum of the salaries is
-- less than $1,000.  Any salary that would exceed the cap must be
-- decreased so that the cap is maintained.
--
-- Any transaction which would result in a negative salary should be
-- aborted and any transaction which would exceed the cap must be decreased
-- and a notice raised.

create function Verify () returns trigger as $$
declare


begin


end;
$$ language 'plpgsql';

create trigger VerifySalary before insert or update on Employee
for each row execute procedure Verify();


----------------------------------------------------------------------
-- Run a test suite.
----------------------------------------------------------------------

-- This insert should fail.
insert into Employee values ('Tom', -100);
select * from Employee;

-- This insert should succeed.
insert into Employee values ('Tom', 100);

-- This insert should succeed.
insert into Employee values ('Jill', 895);

-- This update should be capped.
update Employee set Salary = 1.1 * Salary where Name = 'Tom';
select * from Employee;