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
2. Clear destination and copy everything from source_to_trigger to destination_copy

Step what we will do together

1. Prepare table
2. Create function
3. Create trigger on update and insert
4. 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:

1. This is function. So we must return. It’s void function so we return NULL .
2. We must return TRIGGER in case we want to bind it to TRIGGER .
3. 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');

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