Use ALTER TRIGGER to modify trigger definitions - PolarDB

Renames a trigger, declares an extension dependency, or enables and disables a trigger.

Syntax

Rename a trigger

To rename trigger name on table_name:

ALTER TRIGGER name ON table_name RENAME TO new_name;

Declare an extension dependency

To mark trigger name as dependent on an extension. When the extension is dropped, the trigger is automatically dropped as well.

ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name;

Enable or disable a trigger

To change the status of trigger name in a schema:

ALTER TRIGGER [schema.]name ENABLE | DISABLE;

Trigger names must be unique within the same schema. The status of built-in triggers cannot be changed.

Parameters

Parameter	Description
`schema`	The schema that contains the trigger. Defaults to `public`.
`name`	The name of the trigger to modify.
`new_name`	The new trigger name.
`extension_name`	The name of the extension the trigger depends on.
`ENABLE`	Activates the trigger so it fires on the defined events.
`DISABLE`	Deactivates the trigger so it does not fire.

Examples

Rename a trigger

ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;

Declare an extension dependency

ALTER TRIGGER emp_stamp ON emp DEPENDS ON EXTENSION emplib;

Enable and disable a trigger

The following example shows the trigger emp_audit in its initial enabled state (O in tgenabled), disables it to allow an INSERT that would otherwise be blocked, then re-enables it.

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

After disabling emp_audit, INSERT operations on emp succeed. Re-enabling the trigger restores enforcement.