CREATE TRIGGER fix_favorites
AFTER INSERT OR UPDATE OF results
REFERENCING
  NEW ROW AS newt
FOR EACH ROW
  WHEN (newt.result = 'star baker')
      DELETE FROM favorites
      WHERE baker = NEW.baker AND episodeid = NEW.episodeid ;


CREATE FUNCTION fix_favorites () RETURNS trigger AS $$
BEGIN
    IF NEW.result = 'star baker' THEN
       DELETE FROM favorites
       WHERE baker = NEW.baker AND episodeid = NEW.episodeid ;
    END IF ;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;





--- If I am inserting a tuple to technicals, shift
--- everyone's rank by 1 for that position

CREATE OR REPLACE FUNCTION fix_technicals () RETURNS trigger AS $$
BEGIN
    UPDATE technicals
    SET rank = rank+1
    WHERE rank >= NEW.rank and episodeid = NEW.episodeid;

    RETURN NEW ;
END ;
$$ language plpgsql;

CREATE TRIGGER fix_2 BEFORE INSERT ON technicals
  FOR EACH ROW
  EXECUTE FUNCTION fix_technicals();



--- Before an update, check how much the value is being changed!

CREATE TRIGGER fix_3 BEFORE UPDATE ON technicals
  FOR EACH ROW
  WHEN (abs(OLD.rank-NEW.rank) >= 3)
  EXECUTE FUNCTION fix_technicals2();

CREATE OR REPLACE FUNCTION fix_technicals2 () RETURNS trigger AS $$
BEGIN
    IF NEW.rank < OLD.rank THEN
        NEW.rank = OLD.rank - 1;
    ELSE
        NEW.rank = OLD.rank + 1;
    END IF ;	  
    RETURN NEW ;
END ;
$$ language plpgsql;