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 (
- Triggers can run before, after or instead of the operation attempts to alter the row.
- A trigger set
FOR EACH ROWis called once for every row modified.
FOR EACH STATEMENTexecutes once for the entire operation (0 modified rows would still trigger this).
- Triggers can specify a boolean
WHENcondition 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.
SELECTstatements do not modify rows so no trigger can be set on a
- One SQL command can trigger more than one kind of trigger.
- Use the
DROP TRIGGERcommand to remove a trigger.
- You can query the
information_schema.triggerstable to get a list of triggers in the system.
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_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_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.