Knowledgebase

Introduction

Triggers in PostgreSQL allow developers to define rules and actions which can be executed without their manual intervention, making database management more efficient. By utilizing triggers in a database, users can create a dynamic and responsive database that responds and adapt to the changes made in the application.

This article walks you through the functionality of triggers in PostgreSQL. It demonstrates the steps to create, alter and drop a trigger function.

Prerequisites

• Deploy a RCS Managed Database for PostgreSQL.

• Install the psql CLI tool on your computer to connect to the database.

How Triggers Work

Trigger functions are called when specific actions such as data modification operations like INSERT, DROP, and ALTER, are called. This allows developers to perform event-driven queries within the database.

The following are the components for creating triggers.

• Trigger Event: Specified by a user to run the trigger function.

• Trigger Condition: Specified by a user before the function is executed.

• Trigger Function: Contains SQL statements to perform the desired action.

Below are some user cases for implementing triggers.

• Maintaining Logs

• Data Transformation

• Adding Timestamps

• Data Validation

• Raise Alerts

• Perform Additional Task

Types of Triggers

• Data Definition Langauge (DDL) Triggers

  DDL consists of commands such as CREATE, DROP, and ALTER. When DDL commands are called within the trigger function, it's called a DDL Trigger.

• Data Manipulation Langauge (DML) Triggers

  DML consists of commands such as INSERT, UPDATE, and DELETE. When DML commands are called within the trigger function, it's called a DML Trigger.

• LOGON Triggers

  This consists of the LOGON command which is fired before a user session is established. Therefore all the messages defined by the developer, such as error and success messages are redirected to a SQL server as a SQL Server log.

Set up the Database

In this section, set up a new database with a users table, and users_log to log changes using a trigger in PostgreSQL as described below.

1. Log in to your RCS Managed Database for PostgreSQL.

   $ psql -h mydb.postgres.rcs.is -p 5432 -U admin --dbname postgres
   

   Replace mydb.postgres.rcs.is, 5432, admin with your account RCS Managed Database details.

2. Create a new database named DB1.

   => CREATE DATABASE DB1;
   

3. Switch to the database.

   => \c mydatabase
   

4. Create a new users table.

   CREATE TABLE users
   
   (
   
    user_id INT,
   
    user_name VARCHAR,
   
    address VARCHAR
   
   );
   

   The users table consists of 3 columns, user_id, user_name, and their address. Operations such as insert and update will only run in the users table, and the users_log table consists of similar columns with similar column constraints.

5. Create the users_log table.

   CREATE TABLE users_log
   
   (
   
    old_user_id INT,
   
    old_user_name VARCHAR,
   
    old_address VARCHAR
   
   );
   

6. Verify that the tables are created.

   \d;
   

   Output:

   Schema |   Name    | Type  |  Owner
   
   --------+-----------+-------+----------
   
   public | users     | table | postgres
   
   public | users_log | table | postgres
   

7. Populate data to the users table.

   INSERT INTO public.users 
   
   VALUES (1, 'john', 'Miami, FL'),
   
       (2, 'jane', 'West Palm Beach, FL'),
   
       (3, 'jacob', 'Fort Lauderdale, FL');
   

8. View the table data.

   SELECT * FROM public.users;
   

   Output:

   user_id | user_name |  address
   
   ---------+-----------+-----
   
      1 | john      | Miami, FL
   
      2 | jane      | West Palm Beach, FL
   
      3 | jacob     | Fort Lauderdale, FL
   

9. Verify that the users_log table has no stored data.

   SELECT * FROM public.users_log;
   

   Output:

   old_user_id | old_user_name | old_address
   
   -------------+---------------+-------------
   
   (0 rows)
   

You have created 2 tables named users and users_log. You will implement a trigger function to log any changes made in the users table to the users_log table.

Create a Trigger Function

In this section create a trigger function using the inbuilt CREATE SQL operation followed by the keyword FUNCTION as described below.

1. Create a trigger function.

   CREATE FUNCTION users_log_trg_func()
   
   RETURNS TRIGGER
   
   LANGUAGE 'plpgsql'
   
   AS $$
   
   BEGIN
   
    INSERT INTO public.users_log
   
    (old_user_id,old_user_name,old_address)
   
    VALUES(OLD.user_id,OLD.user_name,OLD.address);
   
   RETURN NEW;
   
   END;
   
   $$;
   

   Below is what the above SQL command does:

   CREATE FUNCTION users_log_trg_func()
   
   RETURNS TRIGGER
   
   LANGUAGE 'plpgsql'
   
   AS $$
   
   BEGIN
   

   The function name users_log_trg_func is suffixed with (). There are multiple functions in PostgreSQL and it's necessary to label the function as a trigger and RETURNS TRIGGER is used for this purpose. plpgsql represents the assigned procedural language supported by the PostgreSQL Object Relational Database Management System (ORDBMS).

   $$ acts as a delimiter used when writing a multi-line string literal. Using $$ specifies that anything written after this should be treated as a single string literal. The BEGIN operator marks the beginning of the function's main executable block.

   INSERT INTO public.users_log
   
   (old_user_id,old_user_name,old_address)
   
   VALUES(OLD.user_id,OLD.user_name,OLD.address);
   

   The above code implements a trigger that uses old data (before performing an update or delete) in the users table, and inserts it in the users_log table, the OLD keyword is used to store only old data in the table.

   RETURN NEW;
   
   END;
   
   $$;
   

   This ensures that the value of the row after the trigger event has occurred. END and $$ ensure that the trigger function's body is concluded without any errors.

Create a Trigger Event

To successfully use a trigger function, create a trigger event as described below.

1. Create a trigger event.

   CREATE TRIGGER users_trig
   
   BEFORE DELETE OR UPDATE
   
   ON public.users
   
   FOR EACH ROW
   
   EXECUTE PROCEDURE users_log_trg_func();
   

   Below is what the above SQL statement does:

   CREATE TRIGGER users_trig
   

   The CREATE operation creates a trigger followed by the keyword TRIGGER to create a trigger named users_trig.

   BEFORE DELETE OR UPDATE
   
   ON public.users
   

   Specifies the event condition on which the trigger must be called. The trigger must be called before a delete or update event is called on a table. For this article, the users table.

   FOR EACH ROW
   

   Defines when it's required for the trigger functions to run for each row.

   EXECUTE PROCEDURE users_log_trg_func();
   

   Links the trigger function to the trigger command.

Output on Executing Trigger Functions

1. A trigger event is activated when an UPDATE or DELETE command runs. In the following command, the trigger function is called automatically.

   UPDATE public.users 
   
   SET user_name='John Doe'
   
   WHERE user_id=1; 
   

   The above command updates the user id 1 in the users table.

2. View the updated users table data.

   SELECT * FROM public.users;
   

   Output:

   user_id | user_name |       address
   
   ---------+-----------+---------------------
   
      2 | jane      | West Palm Beach, FL
   
      3 | jacob     | Fort Lauderdale, FL
   
      1 | John Doe  | Miami, FL
   

3. View the users_log table data.

   SELECT * FROM public.users_log;
   

   Output:

   old_user_id | old_user_name | old_address
   
   -------------+---------------+-------------
   
          1 | john          | Miami, FL
   

   No changes are made manually to the users_log table. But, as soon as the UPDATE command is executed, the trigger function is called to add old data from the users table to the users_log table. The old username, john, is now stored in the users_log table and the updated name John Doe is only available in the main users table.

4. Execute another update statement:

   UPDATE public.users 
   
   SET user_name='Jayne Doe'
   
   WHERE user_id=2; 
   

   The updated users table data should be:

   user_id | user_name |       address
   
   ---------+-----------+---------------------
   
      3 | jacob     | Fort Lauderdale, FL
   
      1 | John Doe  | Miami, FL
   
      2 | Jayne Doe | West Palm Beach, FL
   

5. Verify that the users_log table has more data from the trigger function.

   SELECT * FROM public.users_log;
   

   Output:

   old_user_id | old_user_name |     old_address
   
   -------------+---------------+---------------------
   
          1 | john          | Miami, FL
   
          2 | jane          | West Palm Beach, FL
   

   This shows that the trigger function is working and it's able log old data to the users_log table

Trigger Operations

After creating a trigger function, you can still enable, disable, drop, or alter the function using the following functions.

Disable Trigger

To disable a trigger function, you can use the ALTER TABLE operation to modify the structure or property of the table. This is followed by the table name, and the DISABLE keyword which stops the trigger from making any further changes using the syntax below.

ALTER TABLE table_name DISABLE TRIGGER trigger_name;

For example, disable the users table trigger as below.

ALTER TABLE public.users DISABLE TRIGGER public.users_trig;

Enable Triggers

Similar to disable, you can re-enable the trigger function in case it's disabled using the syntax below.

ALTER TABLE public.users ENABLE TRIGGER public.users_trig;

For example:

ALTER TABLE public.users ENABLE TRIGGER users_trig;

Alter a Trigger

To update a trigger, use the REPLACE keyword. Other body commands can remain the same as before using the syntax below.

CREATE OR REPLACE FUNCTION function_name()

RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

-- Updated function body



RETURN NEW;

END;

$$;

For example:

CREATE OR REPLACE FUNCTION users_log_trg_func()

RETURNS TRIGGER

LANGUAGE 'plpgsql'

AS $$

BEGIN

 INSERT INTO public.users_log

 (old_user_id,old_user_name,old_address)

 VALUES(OLD.user_id,OLD.user_name,OLD.address);

RETURN NEW;

END;

$$;

Delete a Trigger

To permanently delete a trigger, you can use the DROP command. But to avoid any errors, it's a good practice to use IF EXISTS using the syntax below.

DROP TRIGGER [IF EXISTS] trigger_name ON table_name;

For example:

DROP TRIGGER IF EXISTS users_trig ON users;

Optional Functions

BEGIN and END

These are optional commands that are only used when writing multiple condition lines within a trigger function. It's a good practice to use BEGIN and END when writing any new query.

FOR EACH ROW

If FOR EACH ROW is not specified, the trigger function is executed once irrespective of how many rows are being updated. Using FOR EACH ROW enables the trigger to run on each predefined row.

WHERE Clause

The WHERE clause can be used within a trigger function to make sure the trigger is executed only when certain conditions are met. For example:

CREATE FUNCTION users_log_trg_func()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

-- Insert into users_log only if address is 'Miami, FL'

INSERT INTO users_log (old_user_id, old_user_name, old_address)

SELECT OLD.user_id, OLD.user_name, OLD.address

WHERE OLD.address = 'Miami, FL';



RETURN NEW;

END;

$$;

In the above query, the trigger filters the user address column to return values with Miami, FL.

RAISE

Raise logs a warning or notice when a trigger function is executed. It's useful for establishing communication while the trigger function is executed.

CREATE FUNCTION users_log_trg_func()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $$

BEGIN

IF new.user_name IS NULL THEN  

  RAISE NOTICE 'User name is missing';     

END IF;

RETURN NEW;

END;

$$;

The above query establishes a warning communication when the user_id is null. If the condition is true, a notice with the text 'User name is missing' is generated. Other variants of the raise function such as RAISE WARNING or RAISE EXCEPTION offer different levels of severity

Conclusion

In this article, you implemented the fundamental concepts of triggers in PostgreSQL. You also created a trigger function, and set up trigger events to perform operations on triggers and other advanced configurations. It's important to note that although triggers offer more flexibility, a poorly configured trigger will cause significant performance loss and unwanted complications. Therefore, proper planning and testing of such functions is required to make sure the triggers align with the database requirements.

More Information

For more information, visit the following resources.

• PostgreSQL Create Triggers.

• PostgreSQL Alter Triggers.

• Implement PostgreSQL stored procedures.

• Introduction to using Indexes in PostgreSQL.