Updated on 2025-05-29 GMT+08:00

ALTER TRIGGER

Description

ALTER TRIGGER is used to change the name and owner of a trigger.

Currently, only the name and owner of a trigger can be modified.

Precautions

  • The owner of the table where a trigger resides or a user with the ALTER ANY TRIGGER permission can perform the ALTER TRIGGER operation. A system administrator has this permission by default.
  • To modify the owner of a trigger, you must be the owner of the trigger or a system administrator, and you must be a direct or indirect member of the role to which the new owner belongs. The new owner must have the CREATE permission on the schema where the trigger is. When the separation of duties is disabled, SYSADMIN has this permission by default. When the separation of duties is enabled, SYSADMIN does not this permission by default.
  • Only the initial user can change the owner of a trigger to the initial user.

Syntax

  • Rename a trigger.
    ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;

  • Change the owner of a trigger.
    ALTER TRIGGER trigger_name ON table_name OWNER TO new_owner;

Parameters

  • trigger_name

    Specifies the name of the trigger to be modified.

    Value range: an existing trigger.

  • table_name

    Specifies the name of the table where the trigger to be modified is located.

    Value range: an existing table having a trigger.

  • new_name

    Specifies the new name after modification.

    Value range: a string, which complies with the Identifier Naming Conventions. A value contains a maximum of 63 characters and cannot be the same as other triggers on the same table.

  • new_owner

    Name of the new owner of a trigger.

    Value range: an existing username in the database.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Create a source table and a destination table.
gaussdb=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
gaussdb=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

-- Create a DELETE trigger function.
gaussdb=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE plpgsql;

-- Create a DELETE trigger.
gaussdb=# CREATE TRIGGER delete_trigger BEFORE DELETE ON test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE tri_delete_func();

-- Rename a trigger.
gaussdb=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

-- Change the owner of the trigger to jack. (Ensure that jack is a user created in the database.)
gaussdb=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl OWNER TO jack;

-- Drop the trigger.
gaussdb=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;

-- Delete the function.
gaussdb=# DROP FUNCTION tri_delete_func;

-- Delete the source table and destination table.
gaussdb=# DROP TABLE test_trigger_src_tbl;
gaussdb=# DROP TABLE test_trigger_des_tbl;