How to Manage PostgreSQL Triggers

Introduction

In this article we will learn how to manage a PostgreSQL trigger including modifying and disabling trigger. A trigger in PostgreSQL is an operation or function that is called after an event happened. You could have a trigger occur after events such as an update or delete.

Prerequisites

• Basic understanding on how to create PostgreSQL trigger.
• Ensure that PostgreSQL server is properly installed, configured and running on the background.
• To start PostgreSQL server use a LINUX machine use the following command.

• To verify if the service is running use the following command.

• The result should look something like the following:

• To start, stop and restart PostgreSQL server in a Windows machine do the following instruction.

1. Open Control Panel
2. Open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Start, Stop or Restart the service

How to Modify Trigger

We can modify a trigger using the ALTER TRIGGER statement, a PostgreSQL extension of the SQL standard.

The ALTER TRIGGER will change a property of an existing trigger. Below is the syntax of the ALTER TRIGGER statement:

NOTE: Changes can only be made by the owner of the table where the trigger was defined.

Let’s discuss the ALTER TRIGGER statement as stated above.

1. name is the name of an existing trigger to be changed.
2. table_name this is the name of the table were the trigger was defined.
3. new_name this will be the new assigned name for the trigger.

A simple analogy will be, if you have a trigger named “count_updated_rows”, which associated to a table named “grades” and you want to change the name to “num_updated_rows” you will use the ALTER TRIGGER statement like this:

How to use DISABLE TRIGGER

The DISABLE TRIGGER is one way to manage trigger, though PostgreSQL does not specify a specific statement for DISABLE TRIGGER for disabling or even enabling current trigger. This statement is provided by ALTER TABLE and not by ALTER TRIGGER as it has no simple way to provide the option of enabling and disabling all trigger defined in a table in one go.

To disable a trigger using ALTER TABLE statement, use the following below statement.

1. After the ALTER TABLE we specify the name of the table to be disabled.
2. After the DISABLE TRIGGER keyword we specify the name of the trigger associated to the specified table or use the keyword ALL aside from specifying a specific trigger name.

Let us look at the below sample statement to see how we can apply the above syntax in real life application.

The above syntax will disable the last_name_change_log trigger that is defined for the table clients.

Now to disable all triggers defined for the table client simply use the following syntax.

Conclusion

We hope you enjoyed this article on managing triggers in PostgreSQL. We learned about what triggers are and learned how to modify and disable trigger in PostgreSQL. We hope you’re able to apply what you’ve learned to your specific application.

If you’re having trouble with triggers in PostgreSQL please don’t hesitate to reach out to us at Object Rocket.