-- This function raises an exception (aborting the transaction) if some things are 
-- not already found elsewhere in the database

create or replace function careful() returns trigger as '
declare
    myrec RECORD;
begin
   SELECT INTO myrec * FROM students WHERE name=new.name and linux=new.linux;
   IF NOT FOUND THEN
     RAISE EXCEPTION ''student % with linux % not found'',
			new.name, new.linux;	-- printed at each %
  END IF;

-- incorporate a default value. This could have been done in the table definition

  if new.major is null then
     new.major := ''CSC'';
  end if;

-- here we have chosen to look up other values from the students table

  if new.given is null then
     new.given := myrec.given;
  end if;
  if new.email is null then
     new.email := myrec.email;
  end if;

  RETURN NEW;		-- finally, return the NEW row for insert or update
end
' language plpgsql;


--- Once the function is created, you can create a trigger
--- You can replace the function as often as you want after that

--CREATE TRIGGER 
--    care BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE careful();

--- to test the function, you will have to do inserts or updates, only then 
--- will you find out about syntax and logic errors
