In Postgres, we can manipulate the triggers. We can alter a trigger, enable and disable a trigger, create a trigger, or drop a trigger. In this article, we will specifically learn about altering a trigger in Postgres. There are several statements that are commonly used to manipulate the triggers in Postgres and the ALTER statement is one of them. ALTER TRIGGER command is used to rename an existing trigger.

How to Alter a Trigger in PostgreSQL?

To alter a trigger, the ALTER TRIGGER command is used. The basic syntax for the ALTER command in PostgreSQL is given as

ALTER TRIGGER trig_name
 ON tab_name 
 RENAME TO updated_name;

The ALTER TRIGGER is followed by the name of that trigger that is to be altered. ON is followed by the table name to which that trigger is associated. Lastly, the RENAME TO clause is followed by the new or updated name we want to have for that trigger.

Example

Let’s consider an example of the database table possessing the status of the projects being developed in a software house. Let's suppose the query for the table is as follows:

DROP TABLE IF EXISTS project_status; 
CREATE TABLE project_status(
  id INT GENERATED ALWAYS AS IDENTITY,
  proj_name TEXT NOT NULL,
  proj_status TEXT NOT NULL,
  managed_by TEXT NOT NULL,
  PRIMARY KEY(id)
   );

Now insert values to the table:

INSERT INTO project_status( proj_name, proj_status ,managed_by )
  VALUES ('Game app', 'In progress', 'John'),
    ('Chat application', 'Completed', 'Williams'),
  ('Online Food ordering App', 'tested', 'sarah');

The table is given as:

Now consider the case if we want to change the status of the project and log the changes in another table called “project_audits”:

CREATE TABLE project_audits (
  id INT GENERATED ALWAYS AS IDENTITY,
  proj_status VARCHAR(40) NOT NULL,
  updated_on TIMESTAMP(6) NOT NULL
   );

Now create a trigger function called, change_status. The function returns a trigger. The syntax would look like this:

CREATE OR REPLACE FUNCTION change_status()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
   $$
 BEGIN
  IF NEW.proj_status<> OLD.proj_status THEN
   RAISE NOTICE 'The project status was updated';
  END IF;
 RETURN NEW;
 END;
   $$

Here we can see two keywords i.e. OLD and NEW. The OLD keyword refers to the state of the row, i.e. proj_status in this case, before the triggering event has occurred. The NEW keyword refers to the state of the row after the triggering event. So what the code actually does is, it says that if the proj_status (which is a row in the table) before and after the trigger event (that is” update” in this case we will have a look at it) are not equal( <> ) then it will raise notice that “The project status was updated”.

The function has been created:

Now we have to bind the trigger function to the table so we will write the following syntax:

CREATE TRIGGER change_status
  BEFORE UPDATE
  ON project_status
  FOR EACH ROW
  EXECUTE PROCEDURE change_status();

The trigger is created with the name “change_status” and it is specified that the trigger needs to be executed before the update command. The trigger has to be fired on the table”project_status” on each row. After EXECUTE PROCEDURE we write the trigger function declared above.

The trigger is successfully created:

If we update the status of the project with ID 1 from “in Progress” to “completed” we will write the following query:

UPDATE project_status
 SET proj_status = 'Completed'
 WHERE ID = 1;
 SELECT * FROM project_status;

The project status of the project with ID number 1 has been successfully updated:

As seen in the above output, the result of the query is as we expected. Now we will alter the trigger. The syntax will look like this:

ALTER TRIGGER change_status
 ON project_status
 RENAME TO status_update;

The ALTER TRIGGER command was followed by the name of the trigger i.e. change_status which is associated with the table project_status and we have renamed it to “status_update”. The trigger has been successfully altered.

View Triggers in psql

We can view all the triggers associated with a table by running the \dS command in the psql tool. For this, open psql in your local system. We will be connecting to our database where we want to create a table. Enter your password when it asks for “Password for user postgres”. You will see the following:

Now write the command as “\dS your table name”. In my case, it is “\dS project_status”. So this will give all the triggers related to the table.

In the very last line, you can see the trigger we have just altered. So this means that the trigger has been altered and it is the only trigger that is associated with this particular table.

Conclusion

A trigger can be altered in PostgreSQL by using the ALTER TRIGGER command. We can rename the trigger and then check whether the change in name has occurred or not by running some command in the psql shell. This Postgres blog has demonstrated the process of altering a trigger in Postgres.