ALTER TRIGGER v14

Name

ALTER TRIGGER — Change the definition of a trigger.

Synopsis

EDB Postgres Advanced Server supports three variations of the ALTER TRIGGER command. Use the first variation to change the name of a given trigger without changing the trigger definition:

ALTER TRIGGER <name> ON <table_name> RENAME TO <new_name>

Use the second variation of the ALTER TRIGGER command if the trigger depends on an extension. If the extension is dropped, the trigger is dropped as well.

ALTER TRIGGER <name> ON <table_name> DEPENDS ON EXTENSION <extension_name>

Use the third variation of the ALTER TRIGGER command to change the ownership of a trigger's object:

ALTER TRIGGER <name> ON <table_name> AUTHORIZATION <rolespec>

For information about using non-compatible implementations of the ALTER TRIGGER command that are supported by EDB Postgres Advanced Server, see the PostgreSQL core documentation.

Description

ALTER TRIGGER changes the properties of an existing trigger. You must own the table the trigger acts on to change its properties.

To alter an owner of the trigger's implicit object, you can use the ALTER TRIGGER ...ON AUTHORIZATION command. You must have the privilege to execute ALTER TRIGGER ...ON AUTHORIZATION command to assign the trigger's implicit object ownership to a user after authorization.

Parameters

name

The name of the trigger to alter.

table_name

The name of a table on which trigger acts.

rolespec

Determines an owner of trigger objects.

Examples

This example includes the users bob and carol as superusers. The user bob owns a table emp. The user carol owns a trigger named emp_sal_trig, which is created on table emp:

SELECT relname, relowner::regrole FROM pg_class WHERE relname = 'emp';
 relname | relowner
---------+----------
 emp     | bob
(1 row)

SELECT proname, proowner::regrole FROM pg_proc WHERE oid = (SELECT tgfoid
FROM pg_trigger WHERE tgname = 'emp_sal_trig') ORDER BY oid;
 proname           | proowner
-------------------+----------
 emp_sal_trig_emp  | carol
(1 row)

To alter the ownership of table emp from user bob to a new owner edb:

ALTER TABLE emp OWNER TO edb;
ALTER TABLE

SELECT relname, relowner::regrole FROM pg_class WHERE relname = 'emp';
 relname | relowner
---------+----------
 emp     | edb
(1 row)

The table ownership is changed from the user bob to an owner edb, but the trigger ownership of emp_sal_trig isn't altered and is owned by user carol. Alter the trigger emp_sal_trig on table emp, and grant authorization to an owner edb:

ALTER TRIGGER emp_sal_trig ON emp AUTHORIZATION edb;
ALTER TRIGGER

SELECT proname, proowner::regrole FROM pg_proc WHERE oid = (SELECT tgfoid
FROM pg_trigger WHERE tgname = 'emp_sal_trig') ORDER BY oid;
 proname          | proowner
------------------+----------
 emp_sal_trig_emp | edb
(1 row)

The trigger ownership emp_sal_trig on table emp is altered and granted to an owner edb.

See also

CREATE TRIGGER, DROP TRIGGER