PostgreSQL Create Trigger
In this section, we are going to understand the working of the Trigger function, the creation of trigger function, PostgreSQL Create Trigger, and the examples of the Create Trigger command.
What is the Trigger function?
A trigger function is parallel to the consistent user-defined function. But a trigger function can return a value with the type trigger and does not take any parameters.
Syntax of Create trigger function
The syntax for creating a trigger function is as follows:
Note: We can generate a trigger function with the help of any supported languages through PostgreSQL.
A trigger function can accept the data about its calling environment over a special structure called Trigger Data that holds a set of local variables.
For example, before or after the triggering event the OLD and NEW signify the row's states in the table.
PostgreSQL also allows us to other local variables preceded by TG_ like, as TG_WHEN, and TG_TABLE_NAME.
How to Create a New Trigger
We will follow the below process to generate a new trigger in PostgreSQL:
Step1: Firstly, we will create a trigger function with the help of the CREATE FUNCTION command.
Step2: Then, we will fix the trigger function to a table with the help of the CREATE TRIGGER command.
What is PostgreSQL CREATE TRIGGER command?
The CREATE TRIGGER command is used to create a new trigger.
Syntax of PostgreSQL CREATE TRIGGER command:
The syntax of the PostgreSQL CREATE TRIGGER command is as follows:
In the above syntax, we have used the following parameters, as shown in the below table:
Note: A Statement-level trigger is used to implement each transaction, whereas a row-level trigger is used to execute the for each row.
For example, let's assume a table that has 50 rows and two triggers which will be executed when a DELETE event happens.
If the delete command removes 50 rows, the row-level trigger will be implemented 50 times, once for each deleted row. However, a statement-level trigger will be executed for one time irrespective of how many rows are removed.
Example of PostgreSQL Create Trigger
Let us see a sample example to understand the working of the PostgreSQL CREATE Trigger command.
We are creating one new table as Clients with the CREATE command's help and inserting some values using the INSERT command.
To create Clients into an Organization database, we use the CREATE command.
But, before creating the Clients table, we will use the DROP TABLE command if a similar table is already existing in the Organization database.
After executing the above command, we will get the following window message: the Clients table does not exist.
The Clients table contains various columns such as Client_id, First_name, Last_name column, where we use the Client_id as the GENERATED ALWAYS AS IDENTITY constraint.
On executing the above command, we will get the following message, which displays that the Clients table has been created successfully into the Organization database.
Assume that when the name of clients modifies, we want to log the modification in a different table called Client_audits:
After implementing the above command, we will get the following message window, which displays that the Client_audits table has been created successfully into the Organization table.
Now, we will be following the below steps to create a new function for the specified table:
Step1: Creating a new Function
Firstly, we are creating a new function called log_First_name_changes using the below command:
After implementing the above command, we will get the below message window displaying that the log_First_name_changes function has been created successfully into a similar database.
The function inserts the old First name into the Client_audits table, which contains Client_id, First_name, and the time of change if the First_name of a client.
In the above command, we have the following:
Step2: Creating a new Trigger
After creating a new function (log_First_name_changes) successfully, we will fix the trigger function to the Clients table where the trigger_name is First_name_changes.
The trigger function is used to log the modification automatically before the value of the First_name column is updated, as shown in the following command:
We will get the following message on executing the above command, which displays that the First_name_changes trigger has been created successfully.
Step3: Inserting the Data
After creating the new function as log_First_name_changes() and new trigger as First_name_changes successfully, we will enter some values into the Clients table with the INSERT command's help.
After implementing the above command, we will get the following message window, which displays that the two values have been inserted successfully into the Clients table.
Step4: Retrieving the data
After creating and inserting the Clients table's values, we will use the SELECT command to retrieving the data from the Clients table:
After successfully implementing the above command, we will get the below result, which displays that the PostgreSQL returns the data present in the Clients table:
Assume that Olivia Smith modified her First_name to Alivia Smith.
Step5: Updating the First_name
So here, we are updating Olivia's first name to the new one with the help of the UPDATE command, as shown below:
On implementing the above command, we will get the following window message, which displays that the specified value have been updated successfully.
Step7: Verifying the Data after modification
Now, we will verify that if the First name of Olivia has been updated successfully or not using the following SELECT command:
After successfully implementing the above command, we will get the below output: Olivia's first name has been updated to Alivia into the Clients table:
Step8: Validate the contents
After performing all the above steps successfully, in the end, we will validate the contents of the Client_audits table with the help of the following SELECT command:
After executing the above command, we will get the following output, which displays that the modification was logged in the Client_audits table by the trigger.
In the PostgreSQL Create Trigger section, we have learned the following topics: