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.

PostgreSQLprovides the ALTERTRIGGER 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 TRIGGERAS $$
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 UPDATEON 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 TRIGGERstatement 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 INSERTtriggers) will fire.
• If your database has multiple schemas, remember that trigger names are unique within a schema but not across the entire database.