Obligatory Trigger Warning

Jed Rembold

Monday, November 24, 2025

Announcements

  • I’m working on getting you feedback on the last few homework assignments now!
  • Be making progress on your partner project!
    • If you’ve had difficultly contacting your partner, please let me know
  • Next week: presentations!
    • I haven’t heard from anyone about any swaps, so schedule is currently still the same as shown last week
  • Polling today: polling.jedrembold.prof

Review Question

Does the below query run?

INSERT INTO recent_coms 
VALUES
  ('The Batman', 
   'action', 
   '2022-03-04'
  );
  1. Yes
  2. Nope!
CREATE TABLE movies (
  "name" text,
  "genre" text,
  "release" date
);

CREATE VIEW comedies AS
SELECT *
FROM movies
WHERE genre = 'comedy';

CREATE VIEW recent_coms AS
SELECT *
FROM comedies
WHERE release > '2015-01-01'
WITH LOCAL CHECK OPTION;

View Activity

Activity!

  • I’ve given you a simple starter bit of sql here to create and populate some tables. Use them to practice the below tasks.
  • Tasks:
    • Create a view called newest that will only show the “new” boxes from best_boxes
    • Create a view called newred that will only show new and red boxes referencing only newest
    • Adjust the CHECK OPTION setting on one or both views so that you could add ('Jane', 2, 7, 9, 'red', 'new') to newred but not ('Bart', 6, 10, 12, 'red', 'used') to newred
    • Write a function named box_volume to compute the volume of a cubic object, and use that function to populate a new volume column in best_boxes
    • Write a function called scale_boxes which updates the best_boxes table to scale the value in the named width, height, or depth columns by the provided argument. Run it to ensure it is working (SELECT scale_boxes(2);)

Triggered

Triggers

  • Triggers are contingencies that you can set up so that when a certain event occurs, a particular function is run
  • Commonly used to update or change values in the same or other tables when something is newly added or changed in a table
  • Requires two things:
    • A defined trigger function
      • Can’t use the pure SQL language, plpgsql probably best
    • A defined trigger itself

The Trigger Function

  • Initially going to avoid inputs for complexity reasons
    • Technically, this makes the function what Postgres calls a procedure
    • In truth, you can have inputs to better generalize and potentially reuse trigger functions, but we won’t cover that here.
  • You still likely want a way to get information about the inserted/changed/deleted rows
    • The general names NEW and OLD will refer to the newly changed or previous table values (respectively)
  • The function needs to return a special TRIGGER type
  • What you return will depend somewhat on what type of trigger you are making
    • Only really matters for triggers firing before an event
      • UPDATE or INSERT triggers should return NEW
      • DELETE triggers should return OLD

The Trigger Itself

CREATE TRIGGER |||trigger name|||
AFTER UPDATE
  ON |||table_name|||
FOR EACH ROW
EXECUTE FUNCTION |||func_name|||();
  • Need to give the trigger a name
  • Specify when it will trigger
    • Could be before or after a particular event
    • Specifies what table will be “watched”
  • Specify how many times it will trigger
    • Could be for each row affected by the event, or just once for the entire event
  • Specify what function will be run (the earlier defined trigger function)

When to Trigger

  • You can listen for INSERT, UPDATE, or DELETE events that affect a particular table
  • Can trigger before the event occurs
    • Would be before any conditions are checked or changes made
    • What is being returned from your trigger function is important here!
  • Can trigger after the event occurs
    • After all conditions are checked and changes have been made
    • What is returned from the trigger function has no bearing here
  • Technically can also trigger INSTEAD OF the event occuring, but we won’t cover that here

Fine-tuning Triggering

  • In some cases, triggering off of any update or insert is too broad

  • Can localize to just a single (or few) columns by using OF colname1:

    AFTER UPDATE OF |||column_name₁|||, |||column_name₂|||

  • Can localize to only triggering off certain rows by inserting a WHEN condition before the EXECUTE line

    • Can use NEW and OLD within this condition
    WHEN OLD.|||name||| != NEW.|||name|||
EXECUTE FUNCTION |||myfunc|||();

How much triggering

  • Using FOR EACH ROW will cause the trigger function to run once for every row that was a part of the triggered event
    • Run an UPDATE that affected 10 rows? The trigger function will be run 10 times, once on each row
    • Ensures you will have access to the NEW and OLD variables in your trigger function
  • Using FOR EACH STATEMENT will cause the trigger function to run once when the triggering event occurs
    • Run an UPDATE that affects 10 rows? The trigger function just runs once.
    • A statement altering 0 rows will still have an effect!
    • Statement level triggers do not have a way to examine individual modified rows using OLD or NEW

Extra Considerations

  • You can have multiple triggers acting off a single event
    • In this case, Postgres processes them in alphabetical order
  • It is possible to set up cyclical triggers, where Trigger A makes a change that triggers B, which makes a change that then triggers A again, etc
    • Postgres does not have built-in tools to prevent this, and it is up to you not to trigger you way into bad situations!

Activity!

You are very worried about the data within best_boxes from our earlier activity, such that you want to keep a log of any name changes that might occur. The table name_changes was already created for you from the earlier activity.

  • Write a function and corresponding trigger that, upon changing a name from best_boxes, will add a row to the name_changes table, where the operation would be 'update' in this case. The changed_at column should be set to the current timestamp. Test it by changing ‘Bob’ to ‘Bobby’.
  • Write another function and corresponding trigger that will add a row to name_changes whenever a new row is added to best_boxes. In this case, the operation would be 'insert' and the og_name would be NULL.
  • (If time) Add a trigger so that whenever a dimension is altered (like, from your scaling function), the volume is automatically recomputed.

Group Work Time

Touch Base! Have a plan!

  • The remaining time is given over to you and your group to ensure you have a plan in place and will be ready to present next week!
// reveal.js plugins // Added plugins