Triggers - (PostgreSQL Trigger)

Triggers are special stored procedures in PostgreSQL that are automatically executed in response to specified database events. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of triggers in PostgreSQL.

Syntax

CREATE [ CONSTRAINT ] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
[ REFERENCING { OLD | NEW } TABLE [ AS ] reference_name ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE FUNCTION function_name ()

• trigger_name: The name of the trigger.
• event: The database event that will trigger the trigger (e.g. INSERT, UPDATE, DELETE).
• table_name: The name of the table to attach the trigger to.
• referenced_table_name: The name of the table that is referenced by the table_name.
• reference_name: The name of the reference table.
• condition: A condition that must be met for the trigger to be executed.
• function_name: The name of the function to execute when the trigger is activated.

Example

Let's create a trigger that executes when a new record is inserted into a table.

CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
EXECUTE FUNCTION my_function();

In this example, we created a trigger named my_trigger that fires after an insert operation is performed on my_table. The trigger is set to execute the function my_function().

Explanation

In this example, we used the CREATE TRIGGER statement to create a trigger named my_trigger. We specified the event that will activate the trigger (AFTER INSERT), the table that the trigger is attached to (my_table), and the function that will be executed when the trigger is activated (my_function()).

We also specified FOR EACH ROW to indicate that the trigger will be executed once for each row that is affected by the insert operation.

Use

Triggers in PostgreSQL can be used for a number of purposes such as:

• Enforcing data integrity.
• Auditing changes to data.
• Updating related tables.
• Automatic generation of values.
• Restricting access to data.

Important Points

• PostgreSQL supports BEFORE, AFTER and INSTEAD OF triggers.
• Triggers can be defined on both tables and views.
• Triggers can be created for INSERT, UPDATE, DELETE, TRUNCATE and ALTER TABLE statements.
• Triggers can reference OLD and NEW values of changed rows.
• A REFERENCING clause can be used to define aliases for referenced tables.

Summary

In this tutorial, we discussed triggers in PostgreSQL. We covered the syntax for creating triggers, an example of creating a trigger, the output of the example, an explanation of the example, use cases for triggers, and important points to keep in mind when working with triggers. With this knowledge, you can now use triggers in PostgreSQL to create more robust and useful databases.