
-- This script demonstrates triggers.
-- Let's track how many observations each observer has made.

-- The base tables don't include an attribute for that, so the first step is to add such an
-- attribute and ensure it contains appropriate counts initially. Note that this alteration of
-- observer table is only done once. It avoids the trouble associated with dropping and
-- rebuilding the table (and tables that depend on it).

ALTER TABLE observer ADD COLUMN n_observations INTEGER;
UPDATE observer SET n_observations =
  (SELECT COUNT(*) FROM observation WHERE observation.observer_id = observer.id);
ALTER TABLE observer ALTER COLUMN n_observations SET DEFAULT 0;


-- Now let's introduce a function that increments this observation counter, a function that
-- decrements it, and a function that can be used during an update to modify the counters for
-- two observers.

CREATE OR REPLACE FUNCTION increment_observations( ) RETURNS TRIGGER AS $$
BEGIN
  UPDATE observer SET n_observations = n_observations + 1
    WHERE observer.id = NEW.observer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION decrement_observations( ) RETURNS TRIGGER AS $$
BEGIN
  UPDATE observer SET n_observations = n_observations - 1
    WHERE observer.id = OLD.observer_id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION update_observations( ) RETURNS TRIGGER AS $$
BEGIN
  UPDATE observer SET n_observations = n_observations + 1
    WHERE observer.id = NEW.observer_id;
  UPDATE observer SET n_observations = n_observations - 1
    WHERE observer.id = OLD.observer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- Next define the triggers that will fire the functions above. Notice that the UPDATE trigger
-- only fires when a change is made to the observer_id attribute of an existing observation.
-- Changes made to the other attributes don't interest us.

CREATE TRIGGER observations_up AFTER INSERT ON observation
  FOR EACH ROW
  EXECUTE FUNCTION increment_observations();

CREATE TRIGGER observations_down AFTER DELETE ON observation
  FOR EACH ROW
  EXECUTE FUNCTION decrement_observations();

CREATE TRIGGER observations_update AFTER UPDATE OF observer_id ON observation
  FOR EACH ROW
  EXECUTE FUNCTION update_observations();


-- Let's see how this works. First, what is in the observation and observer table?
SELECT * FROM observation;
SELECT fname, lname, n_observations FROM observer;

-- Now let's add an observation of CK Ori associated with observer #1.
INSERT INTO observation
  (name, const, observer_id, location_id, datetime, brightness)
VALUES
  ('CK', 'Ori', 1, 1, '2023-03-30 03:45:00', 7.2);

-- Verify that the record has been added and check if observer #1 has the right n_observations.
SELECT * FROM observation;
SELECT fname, lname, n_observations FROM observer;

-- Oops. Wrong observer! Modify that last record to associate it with observer #2.
UPDATE observation SET observer_id = 2
  WHERE name = 'CK' AND const = 'Ori' AND datetime = '2023-03-30 03:45:00';

-- Verify that the observation got updated and the n_observations were properly handled.
SELECT * FROM observation;
SELECT fname, lname, n_observations  FROM observer;

-- Let's remove that observation.
DELETE FROM observation
  WHERE name = 'CK' AND const = 'Ori' AND datetime = '2023-03-30 03:45:00';

-- Verify that the record is gone and that n_observations were decremented.
SELECT * FROM observation;
SELECT fname, lname, n_observations FROM observer;
