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.
sudo service postgresql start
  • To verify if the service is running use the following command.
service postgresql status
  • The result should look something like the following:
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
  Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
  • 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

Restarting PostgreSQL server in a Windows Machine

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:

ALTER TRIGGER name ON TABLE_NAME RENAME TO new_name

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:

ALTER TRIGGER count_updated_rows
ON grades
RENAME TO num_updated_rows;

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.

ALTER TABLE your_table_name
DISABLE TRIGGER table_trigger_name | ALL
  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.

ALTER TABLE clients
DISABLE TRIGGER last_name_change_log;

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.

ALTER TABLE clients
DISABLE TRIGGER ALL;

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.