Introduction

A trigger is an easy way to enforce a check or constraint, log mutations on a table. It is available for each row or a statement modification.
You can ask the DB to run a function triggered on an action (row append, update, delete) and a clause and timeframe (before/ after the operation).

Ingredients

  • Watch the Youtube video on Postgres trigger for 10 minutes (link)
  • Postgres psql client
  • Any text editor

Writing a Trigger Procedure is different than a Query

  • Find out Postgres datatypes
  • Typecasts using CAST() method
  • Learn Trigger Procedure Syntax

Sample Trigger Procedure

CREATE OR REPLACE FUNCTION time_range_check() RETURNS trigger AS
'
DECLARE
    acceptedTime timestamp;
    newTime timestamp;
    defaultTime timestamp;
  BEGIN
    acceptedTime = LOCALTIMESTAMP(0);
    defaultTime = to_timestamp(1577800000);
    newTime = to_timestamp(CAST(NEW.value as bigint));
	
    IF  newTime < defaultTime  THEN
		 raise EXCEPTION ''invalid past timestamp passed'';
         return NULL;
	END IF;
	IF  newTime > acceptedTime  THEN
		 raise EXCEPTION ''invalid future timestamp passed'';
         return NULL;
	END IF;
	RETURN NEW;
  END;
'LANGUAGE 'plpgsql';

Trigger Code

create trigger my_range_check 
before insert on my_table 
for each row 
when (NEW.item_id = 1)
execute procedure time_range_check();

How to Execute

After composing the trigger procedure and trigger, just paste the text on the psql command line.

How to Debug and Develop

You will get a hint of an error on pasting the trigger code on psql CLI. Every time the procedure code changes, we need to drop the trigger.

# drop trigger my_range_check on my_table;

List Triggers

# \d table_name

Reference

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: