Introduction to PostgreSQL ALTER TRIGGER statement

TheALTER TRIGGER statement allows you to rename a trigger. The following shows the syntax of theALTER TRIGGER statement:

ALTER TRIGGER trigger_nameON table_nameRENAMETO new_trigger_name;

In this syntax:

• First, specify the name of the trigger you want to rename after theALTER TRIGGER keywords.
• Second, provide the name of the table associated with the trigger after theON keyword.
• Third, specify the new name of the trigger after theRENAME TO keyword.

To execute theALTER TRIGGER statement, you must be the owner of the table to which the trigger belongs.

PostgreSQL ALTER TRIGGER example

First,create a new table calledemployees:

DROP TABLE IF EXISTS employees;CREATE TABLE employees(   employee_idINT GENERATED ALWAYS AS IDENTITY,   first_nameVARCHAR(50)NOT NULL,   last_nameVARCHAR(50)NOT NULL,   salarydecimal(11,2)NOT NULL DEFAULT 0,   PRIMARY KEY(employee_id));

Second,create a function that raises an exception if the new salary is greater than the old one 100%:

CREATE OR REPLACE FUNCTION check_salary()  RETURNS TRIGGER  LANGUAGE PLPGSQL  AS$$BEGINIF (NEW.salary- OLD.salary)/ OLD.salary>= 1 THENRAISE'The salary increment cannot that high.';END IF;RETURN NEW;END;$$

Third, create a before-update trigger that executes thecheck_salary() function before updating the salary:

CREATE TRIGGER before_update_salary  BEFORE UPDATE  ON employees  FOR EACHROW  EXECUTE PROCEDURE check_salary();

Fourth,insert a new row into theemployees table:

INSERT INTO employees(first_name, last_name, salary)VALUES('John','Doe',100000);

Fifth, update the salary of the employee id 1:

UPDATE employeesSET salary= 200000WHERE employee_id= 1;

The trigger was fired and issued the following error:

ERROR:  The salary increment cannot that high.CONTEXT:  PL/pgSQL function check_salary()line 4 at RAISESQL state: P0001

It works as expected.

Finally, use theALTER TRIGGER statement to rename thebefore_update_salary trigger tosalary_before_update:

ALTER TRIGGER before_update_salaryON employeesRENAMETO salary_before_update;

If you use psql tool, you can view all triggers associated with a table using the\dS command:

\dS employees

Notice that the letterS is uppercase.

Replacing triggers

PostgreSQL doesn’t support theOR REPLACE statement that allows you to modify the trigger definition like the function that will be executed when the trigger is fired.

To do so, you can use theDROP TRIGGER andCREATE TRIGGER statements. You can also wrap these statements within atransaction.

The following example illustrates how to change thecheck_salary function of thesalary_before_update trigger tovalidate_salary:

BEGIN;DROP TRIGGER IF EXISTS salary_before_updateON employees;CREATE TRIGGER salary_before_udpate  BEFORE UPDATE  ON employees  FOR EACHROW  EXECUTE PROCEDURE validate_salary();COMMIT;

Summary

• Use theALTER TRIGGER statement to rename a trigger.
• Use the pair of theDROP TRIGGER andCREATE TRIGGER statements to replace a trigger with a new one.

Last updated onMarch 30, 2024

On this page

• Introduction to PostgreSQL ALTER TRIGGER statement
• PostgreSQL ALTER TRIGGER example
• Replacing triggers
• Summary