CODEMONDAY
Published in

CODEMONDAY

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.

  1. When source table source_to_trigger update

Step what we will do together

  1. Prepare table

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:

  1. This is function. So we must return. It’s void function so we return NULL .

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');

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();

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store