DbSchema | How to Create a Trigger in PostgreSQL?

Publish on DbSchema Blog >>>

Table of Contents

1. Introduction
2. What is a Trigger?
3. Advantages and Limitations of Using a Trigger
4. Restrictions on Creating a Trigger
5. Types of Triggers and Their Applicability
6. Parameters for Creating a Trigger
7. Step-by-Step Guide to Creating a Trigger in psql and DbSchema
8. Conclusion
9. References

Introduction

PostgreSQL is a powerful open-source relational database management system that provides extensive support for creating triggers. Triggers are database objects that allow you to define custom actions that are automatically executed in response to specified events on a table. This article will explain what triggers are, their advantages and limitations, the restrictions on creating them, and provide a step-by-step guide on how to create triggers using psql and DbSchema.

What is a Trigger?

A trigger in PostgreSQL is a set of SQL statements that are automatically executed when a specific event occurs on a table. These events can include INSERT, UPDATE, or DELETE operations. Triggers can be defined to act either before or after the event, or instead of the event. They can be applied to tables, views, or foreign tables.

Advantages and Limitations of Using a Trigger

Advantages:

Triggers offer several advantages in database management:

• Automation: Triggers allow you to automate certain actions without requiring manual intervention, ensuring data consistency and integrity.
• Data Validation: Triggers can enforce data validation rules, preventing invalid or inconsistent data from being inserted or updated in the database.
• Complex Logic: Triggers enable the execution of complex business logic that cannot be easily achieved through standard SQL operations alone.

Limitations:

However, it is important to consider the limitations of triggers:

• Performance Impact: Poorly designed triggers or triggers with extensive logic can negatively impact database performance, so careful optimization is required.
• Maintenance Complexity: Triggers introduce additional complexity to the database schema, making it harder to understand and maintain.

Restrictions on Creating a Trigger

While PostgreSQL provides great flexibility in defining triggers, there are certain restrictions to keep in mind:

• Triggers cannot be defined on system catalogs or temporary tables.
• For table-level triggers, the table must be specified in the same schema as the trigger.
• Triggers cannot be created on views that include aggregates or window functions.
• Certain statements, such as TRUNCATE or DROP DATABASE, cannot be used within triggers.

Types of Triggers and Their Applicability

Triggers in PostgreSQL can be categorized based on their timing (when they are executed) and their granularity (row-level or statement-level).

The following table summarizes the types of triggers that can be used on tables, views, or foreign tables:

When	Event	Row-level Trigger	Statement-level Trigger
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	—	—

Parameters for Creating a Trigger

When creating a trigger, you need to specify several parameters. Here is a summary of the most important parameters:

Parameter	Description
Name	The name of the trigger.
Event	The event that triggers the execution of the trigger (INSERT, UPDATE, or DELETE).
Table_name	The name of the table on which the trigger is created.
Referenced_table_name	The name of the referenced table, if the trigger is a foreign key trigger.
REFERENCING	The transition relation name for row-level triggers.
Transition_relation_name	The name of the table or view that the trigger is associated with.
Condition	An optional condition that must be satisfied for the trigger to execute.
Function_name	The name of the function to be executed when the trigger fires.
Arguments	Additional arguments to be passed to the trigger function.

Step-by-Step Guide to Creating a Trigger in psql and DbSchema

1. Creating a Trigger in psql:

   • Open a psql session and connect to your PostgreSQL database.

For installation and establishing connection refer to PostgreSQL-How to create a database?

• Make sure you have created a table in your database previously.

For creating a table refer to PostgreSQL-How to create a table?

• Execute the following command to create a trigger:

  CREATE TRIGGER trigger_name
  {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
  ON table_name
  [REFERENCING {OLD | NEW} TABLE transition_relation_name]
  [FOR EACH {ROW | STATEMENT}]
  [WHEN (condition)]
  EXECUTE FUNCTION function_name(arguments);
  

  Replace the placeholders with the appropriate values for your trigger.

Example:

Let’s consider an example where we want to create a trigger named “update_salary_trigger” that automatically updates the “total_salary” column in the “employees” table whenever an update operation is performed on the “salary” column.

CREATE TRIGGER update_salary_trigger
BEFORE UPDATE
ON employees
FOR EACH ROW
WHEN (OLD.salary <> NEW.salary)
EXECUTE FUNCTION update_total_salary();

2. Creating a Trigger in DbSchema:

   • Open DbSchema and connect to your PostgreSQL database.
   • Navigate to the “Triggers” section and click on “Add Trigger”.
   • Fill in the required parameters such as name, event, table name, etc.
   • Specify the trigger function and any additional arguments.
   • If needed, define the referencing table and choose the row-level or statement-level execution.
   • Add a condition if necessary.
   • Save the trigger and apply the changes to the database.

Example:

• Navigate to the “Triggers” section and click on “Add Trigger”.
  • Fill in the parameters as follows:
    • Name: update_salary_trigger
    • Event: BEFORE UPDATE
    • Table_name: employees
    • Transition_relation_name: OLD and NEW
    • Referencing_table_name: leave blank
    • Row-level: checked
    • Condition: OLD.salary <> NEW.salary
    • Function_name: update_total_salary
    • Arguments: leave blank or provide any required arguments

Create Triggers and Visually Manage PostgreSQL using DbSchema

DbSchema is a PostgreSQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Trigger

• Start the application and connect to the Postgres database.
• Navigate to Triggers section and click on Add Trigger.
• Fill in required parameters and save the trigger.

Conclusion

Triggers in PostgreSQL provide a powerful mechanism for automating actions and enforcing data integrity. By understanding what triggers are, their advantages and limitations, the restrictions on creating them, and following the step-by-step guide, you can effectively incorporate triggers into your database management workflow.

References

1. PostgreSQL Official Documentation: https://www.postgresql.org/docs/
2. DbSchema Documentation: https://www.dbschema.com/documentation/
3. SQL Syntax: https://www.w3schools.com/sql/
4. PostgreSQL Tutorial: https://www.postgresqltutorial.com/
5. DbSchema Interactive Diagrams: https://www.dbschema.com

Remember, the official documentation for both PostgreSQL and DbSchema is the most reliable source for up-to-date information. These resources can provide more in-depth knowledge and cover other complex aspects of creating and managing databases.