Trigger functions are a useful tool to keep data in sync. When a database table’s column is modified, a trigger function can be set up to activate and modify the database as appropiate.
We use PL/pgSQL for our trigger functions, which is a procedural programming language part of PostgreSQL, the language our database queries are conducted in.
Editing an Existing Trigger Function
To edit an existing trigger function, simply edit the appropiate file inside the
migrations/migrator/triggers directory. When you next run
or the migrator manually, the trigger functions on the database will be updated
with the changes to the code.
Creating New Trigger Functions
To create a new trigger function,
First, navigate to the
migrations/migrator/triggersdirectory. Then choose between the
coursefolder. Choose the
masterfolder if your trigger function is activated by changes to the master database, and similarly, choose the
courseif your trigger function is activated by changes to the course database.
Create your file
trigger_function_name.sqlwith these contents:
-- -- Name: trigger_function_name(); Type: FUNCTION; Schema: public; Owner: - -- CREATE OR REPLACE FUNCTION public.trigger_function_name() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN (Body) END; $$;
Replace the body with the trigger function. We recommend this tutorial series on how to write them. Check other trigger functions we have for an idea on the syntax. One of the most important ideas to note is that OLD.column represents the column’s date before the edit and NEW.column represents the column’s data after the edit.
Create a migration (see here on how to write a migration). Somewhere in the migration, include the following:
CREATE OR REPLACE FUNCTION public.trigger_function_name() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END; $$; CREATE TRIGGER some_name_about_your_trigger_function BEFORE/AFTER UPDATE/INSERT/DELETE ON public.the_table_your_function_is_activated_by FOR EACH ROW EXECUTE PROCEDURE public.trigger_function_name();
There’s a few things to break down here. Your trigger function can be actived before or after a table has a deletion, an insertion, or a value update on it. Pick either
AFTER. Pick either
DELETE. If you want your function to be activated on, say, an update or an insertion, write instead
UPDATE OR INSERTin the spot above.
Declaring the trigger function above is neccessary so that we can then bind the trigger function to the appropiate action on the table. You do not need the body to be blank, but we recommend it to be to avoid code duplication. The blank body will be replaced by the migrator with the completed body in your other file, so do not worry.
It can be strategic to choose when in your migration you include the code above, as you may want your trigger function binded only after a table is created.
Make sure you include in the down migration section the deletion of the trigger function.
DROP TRIGGER IF EXISTS the_same_name_about_your_trigger_function ON public.the_table_your_function_is_activated_by;