You can execute the ALTER TRIGGER statement to modify trigger definitions, such as the trigger name, the dependency, and the trigger switch.

Syntax

  • Change the trigger name of the table_name table. 
    ALTER TRIGGER name ON table_name RENAME TO new_name
  • Change the plug-in on which the name trigger of the table_name table depends to extension_name. 
    ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name
  • Change the status of the name trigger that is unique in the schema. 
    ALTER TRIGGER [schema.]name ENABLE | DISABLE;
    Note
    • A trigger name must be unique in the same schema.
    • The status of a built-in trigger cannot be changed.

Parameters

ParameterDescription
Parameter Description
schema The namespace where the table on which the trigger depends resides. Default value: public.
name The name of the table on which the trigger depends.
new_name The new name of the trigger.
extenion_name The name of the plug-in on which the trigger depends.
ENABLE/DIABLE Specifies whether the trigger is valid. Valid values:
  • ENABLE: valid.
  • DISABLE: invalid.

Examples

  • Change the name of the trigger. 
    ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;
  • Change the plug-in on which the trigger depends. 
    ALTER TRIGGER emp_stamp ON emp DEPENDS ON EXTENSION emplib;
  • Enable or disable the trigger. 
    testdb=> select tgrelid,tgname,tgenabled from pg_trigger;
 tgrelid |  tgname   | tgenabled
---------+-----------+-----------
   16386 | emp_audit | O
(1 row)
testdb=> insert into emp values(1,'Alice');
ERROR:  INSERT is illegal on emp.
CONTEXT:  PL/pgSQL function process_emp_audit() line 12 at RAISE
testdb=> ALTER TRIGGER emp_audit DISABLE;
ALTER TRIGGER
testdb=> insert into emp values(1,'Alice');
INSERT 0 1
testdb=> ALTER TRIGGER emp_audit ENABLE;
ALTER TRIGGER
testdb=> insert into emp values(2,'Bob');
ERROR:  INSERT is illegal on emp.
CONTEXT:  PL/pgSQL function process_emp_audit() line 12 at RAISE