What is Trigger in PostgreSQL? How Trigger is used in PostgreSQL? PostgreSQL Create Trigger Using pgAdmin How To Create Trigger in PostgreSQL using pgAdmin Listing Triggers using pgAdmin Dropping Triggers using pgAdmin
How Trigger is used in PostgreSQL?
A trigger can be marked with the FOR EACH ROW operator during its creation. Such a trigger will be called once for each row modified by the operation. A trigger can also be marked with the FOR EACH STATEMENT operator during its creation. This trigger will be executed only once for a specific operation.
PostgreSQL Create Trigger
To create a trigger, we use the CREATE TRIGGER function. Here is the syntax for the function: The trigger-name is the name of the trigger. The BEFORE, AFTER and INSTEAD OF are keywords that determine when the trigger will be invoked. The event-name is the name of the event that will cause the trigger to be invoked. This can be INSERT, UPDATE, DELETE, etc. The table-name is the name of the table on which the trigger is to be created. If the trigger is to be created for an INSERT operation, we must add the ON column-name parameter. The following syntax demonstrates this:
PostgreSQL Create Trigger Example
We will use the Price table given below: Price:
Let us create another table, Price_Audits, where we will log the changes made to the Price table: We can now define a new function named auditfunc: The above function will insert a record into the table Price_Audits including the new row id and the time the record is created. Now that we have the trigger function, we should bind it to our Price table. We will give the trigger the name price_trigger. Before a new record is created, the trigger function will be invoked automatically to log the changes. Here is the trigger: Let us insert a new record into the Price table: Now that we have inserted a record into the Price table, a record should also be inserted into the Price_Audit table. This will be as a result of the trigger that we have created on the Price table. Let us check this: This will return the following:
The trigger worked successfully.
Postgres List Trigger
All triggers that you create in PostgreSQL are stored in the pg_trigger table. To see the list of triggers that you have on the database, query the table by running the SELECT command as shown below: This returns the following:
The tgname column of the pg_trigger table denotes the name of the trigger.
Postgres Drop Trigger
To drop a PostgreSQL trigger, we use the DROP TRIGGER statement with the following syntax: The trigger-name parameter denotes the name of the trigger that is to be deleted. The table-name denotes the name of the table from which the trigger is to be deleted. The IF EXISTS clause attempts to delete a trigger that exists. If you attempt to delete a trigger that does not exist without using the IF EXISTS clause, you will get an error. The CASCADE option will help you to drop all objects that depend on the trigger automatically. If you use the RESTRICT option, the trigger will not be deleted if objects are depending on it. For Example: To delete the trigger named example_trigger on table Price, we run the following command: To drop the trigger named example_trigger on the table Company, run the following command:
Using pgAdmin
Now let’s see how all three actions performed using pgAdmin.
How To Create Trigger in PostgreSQL using pgAdmin
Here is how you can create a trigger in Postgres using pgAdmin: Step 1) Login to your pgAdmin account Open pgAdmin and Login to your account using your credentials Step 2) Create a Demo Database
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the Query To create the table Price_Audits, type the query in editor: Step 4) Execute the Query Click the Execute button
Step 5) Run the Code for auditfunc Run the following code to define the function auditfunc: Step 6) Run the Code to create trigger Run the following code to create the trigger price_trigger: Step 7) Insert a new record
Run the following command to insert a new record into the Price table: INSERT INTO Price VALUES (3, 400)
Run the following command to check whether a record was inserted into the Price_Audits table: SELECT * FROM Price_Audits This should return the following:
Step 8) Check the table content Let us check the contents of the Price_Audits table:
Listing Triggers using pgAdmin
Step 1) Run the following command to check the triggers in your database: This returns the following:
Dropping Triggers using pgAdmin
To drop the trigger named example_trigger on the table Company, run the following command:
Summary:
A PostgreSQL trigger refers to a function that is triggered automatically when a database event occurs on a database object, such as a table. Examples of such database events include INSERT, UPDATE, DELETE, etc. A trigger only exists during the lifetime of the database object for which it was created. If the database object is deleted, the trigger will also be deleted. PostgreSQL triggers are created using the CREATE TRIGGER statement. Each trigger is associated with a function stating what the trigger will do when it is invoked.
Download the Database used in this Tutorial
