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:
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:
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:
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:
ALTER TABLE employees
DISABLE TRIGGER log_last_name_changes;
To disable all triggers associated with the
employees table, you use the following statement:
ALTER TABLE employees
DISABLE TRIGGER ALL;
Removing the trigger
To remove an existing trigger definition, you use
DROP TRIGGER statement as follows:
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:
DROP TRIGGER log_last_name_changes ON employees;
In this tutorial, we have shown you how to modify, disable, and remove an existing trigger.