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 employeestable, 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.