PostgreSQL ALTER TRIGGER Statement

Summary: in this tutorial, you will learn how to use the PostgreSQL ALTER TRIGGER statement to rename a trigger.

Introduction to PostgreSQL ALTER TRIGGER statement

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

ALTER TRIGGER trigger_name
ON table_name 
RENAME TO new_trigger_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

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

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

PostgreSQL ALTER TRIGGER example

First, create a new table called employees:

DROP TABLE IF EXISTS employees;

CREATE TABLE employees(
   employee_id INT GENERATED ALWAYS AS IDENTITY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   salary decimal(11,2) NOT NULL DEFAULT 0,
   PRIMARY KEY(employee_id)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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
$$
BEGIN
	IF (NEW.salary - OLD.salary) / OLD.salary >= 1 THEN
		RAISE 'The salary increment cannot that high.';
	END IF;

	RETURN NEW;
END;
$$Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

CREATE TRIGGER before_update_salary
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE check_salary();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, insert a new row into the employees table:

INSERT INTO employees(first_name, last_name, salary)
VALUES('John','Doe',100000);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fifth, update the salary of the employee id 1:

UPDATE employees
SET salary = 200000
WHERE employee_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 RAISE
SQL state: P0001Code language: Shell Session (shell)

It works as expected.

Finally, use the ALTER TRIGGER statement to rename the before_update_salary trigger to salary_before_update:

ALTER TRIGGER before_update_salary
ON employees
RENAME TO salary_before_update;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

 \dS employeesCode language: Shell Session (shell)

Notice that the letter S is uppercase.

Replacing triggers

PostgreSQL doesn’t support the OR 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 the DROP TRIGGER and CREATE TRIGGER statements. You can also wrap these statements within a transaction.

The following example illustrates how to change the check_salary function of the salary_before_update trigger to validate_salary:

BEGIN;

DROP TRIGGER IF EXISTS salary_before_update 
ON employees;

CREATE TRIGGER salary_before_udpate
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE validate_salary();

COMMIT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

• Use the ALTER TRIGGER statement to rename a trigger.
• Use the pair of the DROP TRIGGER and CREATE TRIGGER statements to replace a trigger with a new one.