Learn

In this lesson on triggers in PostgreSQL we covered how:

  • Triggers are associated with a specific table, view or foreign table.
  • Triggers execute a specified function when certain operations are performed on the table (INSERT, UPDATE, DELETE, TRUNCATE).
  • Triggers can run before, after or instead of the operation attempts to alter the row.
  • A trigger set FOR EACH ROW is called once for every row modified.
  • FOR EACH STATEMENT executes once for the entire operation (0 modified rows would still trigger this).
  • Triggers can specify a boolean WHEN condition to see when they should be fired.
  • Multiple triggers of the same kind can exist on the same table. If so they are triggered in alphabetical order.
  • SELECT statements do not modify rows so no trigger can be set on a SELECT statement.
  • One SQL command can trigger more than one kind of trigger.
  • Use the DROP TRIGGER command to remove a trigger.
  • You can query the information_schema.triggers table to get a list of triggers in the system.

Instructions

1.

You can use the workspace here to experiment with what you have learned about triggers. The tables you have to work with are the customers and customers_log tables. As a tip, for the customers table, when you SELECT * an ORDER BY customer_id might be helpful.

Three functions have been created named function_1, function_2, and function_3. See if you can figure out what each does based on how you set up your triggers to call them.

Note, they were given terrible names on purpose to not give away their purpose and let you discover through experimentation. As a hint, you have used all of these functions during this lesson in previous exercises — the names have just been changed to make them mysterious.

Here’s an example trigger creation as a reminder:

CREATE TRIGGER <trigger_name> BEFORE UPDATE ON <table_name> FOR EACH ROW EXECUTE PROCEDURE <function_name>();

Don’t forget, if you write a trigger for each function, and those triggers activate on the same action, say an UPDATE, all three functions will run when you update the table.

Have fun! You can check the hint for the answer to what the functions do.

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?