PostgreSQL ALTER TRIGGER

In this section, we are going to understand the working of the PostgreSQL ALTER TRIGGER command and see the example of altering a trigger or rename a trigger from a specified table in PostgreSQL.

What is PostgreSQL ALTER TRIGGER command?

In PostgreSQL Trigger, the next command is the Alter Trigger command, which is used to rename the existing trigger.

The syntax of PostgreSQL Alter trigger command

The following illustration is used to alter a trigger from a table:

ALTER TRIGGER trigger_name
ON table_name 
RENAME TO new_trigger_name;

In the above syntax, we have used the following parameters:

Parameters	Description
Trigger_name	It is used to define the trigger name that we need to rename, and it is mentioned after the ALTER TRIGGER keyword.
Table_name	The table_name parameter is used to define the table name, which is connected to the trigger. And it is used after the ON keyword.
New_trigger_name	It is used to specify the new name of the trigger. And it is written after the RENAME TO keyword.

Example of PostgreSQL ALTER TRIGGER command

Let us see a sample example to understand the working of the PostgreSQL Alter Trigger command.

We are creating one new table as Student with the CREATE command's help and inserting some values using the INSERT command.

Step1: Creating a new table

To create a Student into an Organization database, we use the CREATE command.

But, before creating the Student table, we will use the DROP TABLE command to check if a similar table is already existing in the Organization database or not.

Output

After executing the above command, we will get the following window message: The Student table does not exist.

The Student table contains various columns such as Student_id, Student_name, Scholarship column, where we use the Student_id as the GENERATED ALWAYS AS IDENTITY constraint.

CREATE TABLE Student(
   Student_id INT GENERATED ALWAYS AS IDENTITY,
   Student_name VARCHAR(50) NOT NULL,
   Scholarship decimal(11,2) not null default 0,
   PRIMARY KEY(Student_id)
);

Output

On executing the above command, we will get the following message: The Student table has been created successfully into the Organization database.

Step2: Creating a new function

After creating the Student table successfully, we will create a new function, which raises an exception if the new scholarship is larger than the old one 100%:

CREATE OR REPLACE FUNCTION check_scholarship()
  RETURNS TRIGGER 
  LANGUAGE plpgsql  
  AS
$$
BEGIN
	IF (NEW. scholarship - OLD. scholarship) / OLD. scholarship >= 1 THEN
		RAISE 'The scholarship raise cannot that high.';
	END IF;

	RETURN NEW;
END;
$$

Output

On executing the above command, we will get the following message, which displays that the check_scholarship() function has been created successfully into the Organization database.

Step3: Creating a new Trigger

After creating the check_scholarship() function, we will create a new trigger on the Student table before update trigger that execute the check_scholarship() function before updating the scholarship.

CREATE TRIGGER before_update_scholarship
  BEFORE UPDATE
  ON Student
  FOR EACH ROW
  EXECUTE PROCEDURE check_scholarship ();

Output

After implementing the before_update_scholarship above command, we will get the following message window, which displays that the specified trigger has been inserted successfully for the Student table.

Step4: Insert a new value

Once the function and trigger have been generated successfully, we will insert a new row with the INSERT's command help into the Student table:

INSERT INTO Student(Student_name, scholarship)
VALUES('Mike Ross',100000);

Output

After implementing the above command, we will get the below message window, displaying that the particular value has been inserted successfully into the Student table.

Step5: Updating the Value

After inserting the new row, we will update the scholarship of the Student_id 1 using the below UPDATE command:

UPDATE Student
SET scholarship = 200000
WHERE Student_id = 1;

Output

On implementing the above command, the trigger was executed and raise an error, which says that the scholarship raise cannot be that high.

Step5: Altering the trigger command

To resolve the above error, we will use the ALTER TRIGGER command to rename the before_update_scholarship trigger to scholarship_before_update.

ALTER TRIGGER before_update_scholarship
ON Student
RENAME TO scholarship_before_update;

Output

After implementing the above command, we will get the below message window, which displays that the particular trigger has been renamed successfully from before_update_scholarship trigger to scholarship_before_update.

View Trigger SQL shell (PSQL)

We are going to follow the below process to view a Trigger in psql:

Step1

Firstly, we will open the psql in our local system, and we will connect to the database where we want to create a table.

Step2

For connecting an Organization database, we will enter the below command:

Output

After executing the above command, we will get the following output:

Step3

We will now enter the below command to view all triggers linked with a table in the Organization database.

Output

On implementing the above command, we will get the below output, which displays the existing Trigger, which is scholarship_before_update present in the Organization database:

Note: In the\dS command, the letter S is in the Uppercase, or if we use the \ds where the letter s is in the lowercase, it will occur the below error:

Changing the triggers

In PostgreSQL, does not contain the OR REPLACE command, which provides us to change the trigger explanation like the function, which will be implemented when the trigger is executed.

Therefore, we can wrap these commands in a transaction, and also use the CREATE TRIGGER and DROP TRIGGER commands.

Let us see one sample example to understand how the DROP TRIGGER and CREATE TRIGGER command works in a transaction.

The below command represents how to change the check_scholarship() function of the scholarship_before_update trigger to validate_scholarship:

Step1: Begin a Transaction

To start a transaction, we can use the following statement:

Output

After implementing the above command, we will get the below message window, which says that the specified command has been successfully implemented.

Step2: Using the DROP trigger command

After successfully starting the transaction process, we will execute the following DROP TRIGGER command:

DROP TRIGGER IF EXISTS scholarship_before_update 
on Student;

Output

After implementing the above command, we will get the below output, which displays that the particular trigger has been dropped successfully from the Student table.

Step3: Create a new Trigger

After dropping the scholarship_before_update trigger successfully, we create a new trigger scholarship_before_udpate with a similar name, as shown in the below command:

CREATE TRIGGER scholarship_before_udpate
  BEFORE UPDATE
  ON Student
  FOR EACH ROW
  EXECUTE PROCEDURE check_scholarship();

Output

After implementing the above command, we will get the following message window, which displays that the particular trigger has been inserted successfully for the Student table.

Step4: Commit the transaction

To make the change visible to other sessions (or users), we need to commit the transaction with the help of the COMMIT command, as shown below:

Output

After implementing the above command, we will get the following message window, which displays that the transaction has been committed successfully for the Student table.

Overview

In the PostgreSQL ALTER Trigger section, we have learned the following topics:

To rename a trigger, we have used the PostgreSQL Alter Trigger command with an example.
To change a trigger with a new one, we have used the pair of the DROP TRIGGER and CREATE TRIGGER commands.

Next TopicPostgreSQL DISABLE TRIGGER

← prev next →