How To Create a Trigger On PostgreSQL

 

With using triggers in PostgreSQL, you can trigger another event before an event occurs (BEFORE TRIGGER).

Or you can trigger another event after it happens (AFTER TRIGGER).

Or you can replace this event with another event before the event occurs (INSTEAD OF TRIGGER).

Let’s make some examples of the trigger types we mentioned above.

First, create a few tables with the help of the following scripts and add some records to the trigger_example table.

 

BEFORE TRIGGER:

Let’s move on by making an example.

We will create a before trigger.

This trigger will transfer the new and old state of that record to the trigger_example_updated table before a record’s value changes in the trigger_example table.

First, we create a procedure to use in trigger.

 

Then we create the before trigger as follows.

 

Lets update a record to test the trigger.

 

As you can see, the trigger works successfully.

 

AFTER TRIGGER:

In this trigger type, the trigger is triggered after the event occurs.

This is the only difference from Before Trigger.

You can repeat the example we made for before trigger for after trigger.

 

INSTEAD OF TRIGGER:

In this trigger type, we trigger another event instead of an actual event.

Create a procedure as follows.

 

Later, when we want to create the INSTEAD OF Trigger, we will get an error as below.

As you can see, we can not create an INSTEAD OF Trigger for tables.

 

ERROR:  “trigger_example” is a table

DETAIL:  Tables cannot have INSTEAD OF triggers.

 

Lets create a view as follows.

 

Recreate the INSTEAD OF Trigger on this view in the following way.

You may want to look at the following articles about Views.

How To Create a View On PostgreSQL“,

How To Create a Materialized View On PostgreSQL“,

How To Create a Recursive View On PostgreSQL“,

How To Create an Updateable View On PostgreSQL“,

How To Create an Updateable View WITH CHECK CONSTRAINT On PostgreSQL

 

 

Let’s test the INSTEAD OF Trigger in the following way.

It worked as you see it.

 

I get the following table from postgresql.org.

I think its useful.

The following table summarizes which types of triggers may be used on tables, views, and foreign tables:

When

Event

Row-level

Statement-level

BEFORE

INSERT/UPDATE/DELETE

Tables and foreign tables

Tables, views, and foreign tables

TRUNCATE

Tables

AFTER

INSERT/UPDATE/DELETE

Tables and foreign tables

Tables, views, and foreign tables

TRUNCATE

Tables

INSTEAD OF

INSERT/UPDATE/DELETE

Views

TRUNCATE

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *