How to Manage PostgreSQL Triggers
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.
- 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: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
- To start, stop and restart PostgreSQL server in a Windows machine do the following instruction.
- Open Control Panel
- Open Administrative Tools
- Open Services
- Find the PostgreSQL Server service
- 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.
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.
- name is the name of an existing trigger to be changed.
- table_name this is the name of the table were the trigger was defined.
- 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
RENAME TO num_updated_rows;
How to use DISABLE TRIGGER
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
- After the
ALTER TABLEwe specify the name of the table to be disabled.
- After the
DISABLE TRIGGERkeyword we specify the name of the trigger associated to the specified table or use the keyword
ALLaside 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
Now to disable all triggers defined for the table
client simply use the following syntax.
ALTER TABLE clients
DISABLE TRIGGER ALL;
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