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:

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

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.

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%:

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.

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:

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:

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.

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:

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:

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.