Summary: this tutorial shows you how to manage PostgreSQL trigger including modifying, disabling, and removing the trigger.
Modifying the trigger
To modify the trigger, you use ALTER TRIGGER
statement. This statement is a PostgreSQL extension of the SQL standard. The syntax of the ALTER TRIGGER
statement is as follows:
1 2 | ALTER TRIGGER trigger_name ON table_name RENAME TO new_name; |
First, you specify the name of trigger associated with a particular table that you want to change.
Second, you put the new trigger name in the RENAME TO
clause.
For example, if you want to change the last_name_changes
trigger, which is associated with the employees
table, to log_last_name_changes
, you use the ALTER TRIGGER
statement as follows:
1 2 | ALTER TRIGGER last_name_changes ON employees RENAME TO log_last_name_changes; |
Disabling the trigger
PostgreSQL does not provide any specific statement such as DISABLE TRIGGER
for disabling an existing trigger. However, you can disable a trigger using ALTER TABLE statement as follows:
1 2 | ALTER TABLE table_name DISABLE TRIGGER trigger_name | ALL |
You specify the trigger name after the DISABLE TRIGGER
clause to disable a particular trigger. To disable all triggers associated with a table, you use ALL
instead of a particular trigger name.
Notice that a disabled trigger is still available in the database. However, it is not fired when its triggering event occurs.
Suppose you want to disable the log_last_name_changes
trigger associated with the employees
table, you can use the following statement:
1 2 | ALTER TABLE employees DISABLE TRIGGER log_last_name_changes; |
To disable all triggers associated with the employees
table, you use the following statement:
1 2 | ALTER TABLE employees DISABLE TRIGGER ALL; |
Removing the trigger
To remove an existing trigger definition, you use DROP TRIGGER
statement as follows:
1 | DROP TRIGGER [IF EXISTS] trigger_name ON table_name; |
You specify the trigger name that you want to remove after DROP TRIGGER
clause and the table that the trigger is associated with.
To avoid error of removing non-existent trigger, you use the IF EXISTS
option.
For example, to remove log_last_name_changes
trigger, you use the following statement:
1 | DROP TRIGGER log_last_name_changes ON employees; |
In this tutorial, we have shown you how to modify, disable, and remove an existing trigger.