PostgreSQL Trigger: Learning by Doing in 10 minutes
In this tutorial, we will learn trigger by building auto copy record from source table to destination table.
- When source table
source_to_trigger
update - Clear destination and copy everything from
source_to_trigger
todestination_copy

Step what we will do together
- Prepare table
- Create function
- Create trigger on update and insert
- Testing the trigger
Step 1: Prepare table and data
First of all create all the table
CREATE TABLE source_to_trigger (
id uuid DEFAULT uuid_generate_v4(),
title TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT source_to_trigger_pkey PRIMARY KEY (id)
);
CREATE TABLE destination_copy (
id uuid DEFAULT uuid_generate_v4(),
title TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT destination_copy_pkey PRIMARY KEY (id)
);
If you got error from uuid
, then you should enable by
CREATE EXTENSION "uuid-ossp";
Step 2: Create function (Procedure)
In PostgreSQL, “Procedure” is one kind of function.
CREATE OR REPLACE FUNCTION copy_record() RETURNS TRIGGER AS
$$
BEGIN
TRUNCATE TABLE destination_copy;
INSERT INTO destination_copy SELECT * FROM source_to_trigger;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Some important note here:
- This is function. So we must return. It’s void function so we return
NULL
. - We must return
TRIGGER
in case we want to bind it toTRIGGER
. - The sign
$$
can have any name in between$
e.g.,$copy_record$
or we can leave it like that.
Step 3: Bind function to trigger
CREATE TRIGGER copy_record_trigger_update
AFTER UPDATE ON source_to_trigger
EXECUTE PROCEDURE copy_record();
CREATE TRIGGER copy_record_trigger_insert
AFTER INSERT ON source_to_trigger
EXECUTE PROCEDURE copy_record();
If you have Dbeaver or other client tool, you can see it appear

Step 4: Testing insert and update
Test by insert the record into the table
INSERT INTO source_to_trigger (title)
VALUES ('foo'), ('bar'), ('baz');
VALUES ('foo'), ('bar'), ('baz');

Test by update the record on the source table

Here we go.
Hope this help !
Appendix
When there is slip in the step you can delete and begin again
DROP TRIGGER copy_record_trigger_insert ON source_to_trigger;
DROP TRIGGER copy_record_trigger_update ON source_to_trigger;
DROP FUNCTION copy_record();
DROP TRIGGER copy_record_trigger_update ON source_to_trigger;
DROP FUNCTION copy_record();