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

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