How To Create a Trigger On PostgreSQL

dbtut

6 years ago

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.

CREATE TABLE trigger_example (name VARCHAR(50));
CREATE TABLE trigger_example_deleted (name VARCHAR(50));
CREATE TABLE trigger_example_inserted (name VARCHAR(50));
CREATE TABLE trigger_example_updated (newname VARCHAR(50), oldname VARCHAR(50));
INSERT INTO trigger_example (name) VALUES('Nurullah CAKIR'),('Keanu Reaves');

CREATE TABLE trigger_example (name VARCHAR(50));

CREATE TABLE trigger_example_deleted (name VARCHAR(50));

CREATE TABLE trigger_example_inserted (name VARCHAR(50));

CREATE TABLE trigger_example_updated (newname VARCHAR(50), oldname VARCHAR(50));

INSERT INTO trigger_example (name) VALUES('Nurullah CAKIR'),('Keanu Reaves');

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.

CREATE OR REPLACE FUNCTION update_function()RETURNS trigger AS $xx$
BEGIN
 INSERT INTO trigger_example_updated(newname,oldname)
 VALUES(NEW.name,OLD.name);
 RETURN NEW;
END;
$xx$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_function()RETURNS trigger AS $xx$

BEGIN

INSERT INTO trigger_example_updated(newname,oldname)

VALUES(NEW.name,OLD.name);

RETURN NEW;

END;

$xx$ LANGUAGE plpgsql;

Then we create the before trigger as follows.

CREATE TRIGGER Before_Example
    BEFORE UPDATE ON trigger_example
    FOR EACH ROW
    WHEN (OLD.name <> NEW.name)
    EXECUTE PROCEDURE update_function();

CREATE TRIGGER Before_Example

BEFORE UPDATE ON trigger_example

FOR EACH ROW

WHEN (OLD.name <> NEW.name)

EXECUTE PROCEDURE update_function();

Lets update a record to test the trigger.

UPDATE trigger_example SET name='Nuru Cakir' WHERE name='Nurullah CAKIR';

1	UPDATE trigger_example SET name='Nuru Cakir' WHERE name='Nurullah CAKIR';

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.

CREATE TRIGGER InsteadOf_Example
    INSTEAD OF INSERT ON trigger_example
    FOR EACH ROW
    EXECUTE PROCEDURE insteadof_function();

CREATE TRIGGER InsteadOf_Example

INSTEAD OF INSERT ON trigger_example

FOR EACH ROW

EXECUTE PROCEDURE insteadof_function();

ERROR: “trigger_example” is a table

DETAIL: Tables cannot have INSTEAD OF triggers.

Lets create a view as follows.

CREATE VIEW trigger_example_view AS
SELECT name FROM trigger_example;

1 2	CREATE VIEW trigger_example_view AS SELECT name FROM trigger_example;

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”

CREATE TRIGGER InsteadOf_Example
    INSTEAD OF INSERT ON trigger_example_view
    FOR EACH ROW
    EXECUTE PROCEDURE insteadof_function();

CREATE TRIGGER InsteadOf_Example

INSTEAD OF INSERT ON trigger_example_view

FOR EACH ROW

EXECUTE PROCEDURE insteadof_function();

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

It worked as you see it.

INSERT INTO trigger_example_view (name) VALUES('InsteadOfExamples');
SELECT * FROM trigger_example;
SELECT * FROM trigger_example_inserted;

INSERT INTO trigger_example_view (name) VALUES('InsteadOfExamples');

SELECT * FROM trigger_example;

SELECT * FROM trigger_example_inserted;

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	—	—