Trending September 2023 # Postgresql Triggers: Create, List &Amp; Drop With Example # Suggested October 2023 # Top 12 Popular

You are reading the article Postgresql Triggers: Create, List &Amp; Drop With Example updated in September 2023 on the website Lanphuongmhbrtower.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Postgresql Triggers: Create, List &Amp; Drop With Example

What is Trigger in PostgreSQL?

A PostgreSQL Trigger is a function that is triggered automatically when a database event occurs on a database object. For example, a table. Examples of database events that can activate a trigger include INSERT, UPDATE, DELETE, etc. Moreover, when you create a trigger for a table, the trigger will be dropped automatically when that table is deleted.

In this PostgreSQL Trigger tutorial, you will learn:

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:

ON table-name [ — Trigger logic ];

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:

CREATE TRIGGER trigger-name AFTER INSERT ON column-name ON table-name [ -- Trigger logic ]; 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:

CREATE TABLE Price_Audits ( book_id INT NOT NULL, );

We can now define a new function named auditfunc:

CREATE OR REPLACE FUNCTION auditfunc() RETURNS TRIGGER AS $my_table$ BEGIN RETURN NEW; END; $my_table$ LANGUAGE plpgsql;

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:

CREATE TRIGGER price_trigger AFTER INSERT ON Price FOR EACH ROW EXECUTE PROCEDURE auditfunc();

Let us insert a new record into the Price table:

INSERT INTO Price VALUES (3, 400);

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:

SELECT * FROM Price_Audits;

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:

SELECT tgname FROM pg_trigger;

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:

DROP TRIGGER [IF EXISTS] trigger-name

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:

DROP TRIGGER example_trigger IF EXISTS ON Company; 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

Step 3) Type the Query

To create the table Price_Audits, type the query in editor:

CREATE TABLE Price_Audits ( book_id INT NOT NULL, )

Step 4) Execute the Query

Step 5) Run the Code for auditfunc

Run the following code to define the function auditfunc:

CREATE OR REPLACE FUNCTION auditfunc() RETURNS TRIGGER AS $my_table$ BEGIN RETURN NEW; END; $my_table$ LANGUAGE plpgsql

Step 6) Run the Code to create trigger

Run the following code to create the trigger price_trigger:

CREATE TRIGGER price_trigger AFTER INSERT ON Price FOR EACH ROW EXECUTE PROCEDURE auditfunc()

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:

SELECT tgname FROM pg_trigger

This returns the following:

Dropping Triggers using pgAdmin

To drop the trigger named example_trigger on the table Company, run the following command:

DROP TRIGGER example_trigger IF EXISTS ON Company 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

You're reading Postgresql Triggers: Create, List &Amp; Drop With Example

Update the detailed information about Postgresql Triggers: Create, List &Amp; Drop With Example on the Lanphuongmhbrtower.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!