=========================================================================
PLEASE DO NOT CREATE THE FUNCTIONS HERE OR ANY OBJECTS IN THE SHARED
DATABASES IN THE CLASS SERVER.

USE YOUR OWN DATABASE.

LOOK AT THE LEFT DROPDOWN FOR DATABASES AND CHOOSE: db_<yourusername>
=========================================================================

Triggers:
-------------

<Do not use common databases for creating tables/functions/triggers in
the class server>

Documentation here:

https://www.postgresql.org/docs/13/triggers.html


Database level constraints:
-----------------------------

- People in the favorites relation should not be star bakers

- Add numwins attribute to bakers: numwins should be equal to the
  number of star baker wins

- Add versioning: table ep_history should contain past data from
  episodes table


Assertions vs. Triggers:

Assertion: what must be true in database (SQL standard)

Triggers: programming approach, how to fix issues an
          update/delete/insert may cause

Triggering events: update/delete/insert
Under certain conditions: when statement
What to do: a function/procedure

Row level (for each row) or statement level

If not careful:

- Infinite loops
- Bad data

Any trigger that is activated becomes part of the transaction!


Basic structure:
-------------------------------------

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();


--------------------------------------

NEW

    Data type RECORD; variable holding the new database row for
    INSERT/UPDATE operations in row-level triggers. This variable is
    null in statement-level triggers and for DELETE operations.

OLD

    Data type RECORD; variable holding the old database row for
    UPDATE/DELETE operations in row-level triggers. This variable is
    null in statement-level triggers and for INSERT operations.



-- Fix baker names before they are inserted


CREATE FUNCTION fix_baker () RETURNS trigger AS $$
    BEGIN
        NEW.baker = initcap(trim(NEW.baker));    
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fix_baker BEFORE INSERT OR UPDATE ON bakers
    FOR EACH ROW EXECUTE FUNCTION fix_baker();


-- People in the favorites relation should not be star bakers
-- If inserting someone as a star baker, remove them from favorites


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;

CREATE TRIGGER fix_favorites AFTER INSERT OR UPDATE ON results
    FOR EACH ROW EXECUTE FUNCTION fix_favorites();