PostgreSQL – ALTER TRIGGER

In PostgreSQL, triggers are a powerful mechanism to automate tasks and enforce business rules. Sometimes, you may need to modify a trigger, such as renaming it, to reflect changes in your application or database design.

PostgreSQL provides the ALTER TRIGGER statement for this purpose, an extension of the SQL standard.

Syntax

ALTER TRIGGER trigger_name ON table_name
RENAME TO new_name;

Parameters:

Let’s analyze the above syntax:

• trigger_name: The name of the trigger you want to modify.
• table_name: The table to which the trigger is associated.
• RENAME TO new_name: The new name for the trigger.

PostgreSQL ALTER TRIGGER Example

Let us take a look at an example of ALTER TRIGGER Examples in PostgreSQL to better understand the concept.

Step 1: Create the Staff Table

First, we create a staff table for demonstration with the below statement:

CREATE TABLE staff(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
);

Step 2: Create a Validation Function

Create a function that validates the username of a staff. The username of staff must not be null and its length must be at least 8.

CREATE FUNCTION check_staff_user()
    RETURNS TRIGGER
AS $$
BEGIN
    IF length(NEW.username) < 8 OR NEW.username IS NULL THEN
        RAISE EXCEPTION 'The username cannot be less than 8 characters';
    END IF;
    IF NEW.NAME IS NULL THEN
        RAISE EXCEPTION 'Username cannot be NULL';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Step 3: Create a Trigger

Create a new trigger on the staff table to check the username of a staff. This trigger will fire whenever you insert or update a row in the staff table.

CREATE TRIGGER username_check 
    BEFORE INSERT OR UPDATE
ON staff
FOR EACH ROW 
    EXECUTE PROCEDURE check_staff_user();

Step 4: Modify the Trigger

Now to modify the above-created trigger use the below statement:

ALTER TRIGGER username_check ON staff
RENAME TO check_username;

Output:

Verification

To verify the changes, you can check the list of triggers on the ‘staff' table:

SELECT tgname
FROM pg_trigger
WHERE tgrelid = 'staff'::regclass;

This query will return the list of triggers associated with the ‘staff' table, allowing you to confirm that the trigger has been renamed successfully.

Important Points About PostgreSQL ALTER TRIGGER

• The ALTER TRIGGER statement in PostgreSQL is primarily used for renaming triggers.
• To execute the ALTER TRIGGER statement, you need ownership of the table on which the trigger is defined.
• PostgreSQL does not guarantee the order in which triggers of the same type (e.g., multiple BEFORE INSERT triggers) will fire.
• If your database has multiple schemas, remember that trigger names are unique within a schema but not across the entire database.