Joining Data and using Triggers in PostgreSQL – Learn how to extract data from multiple tables and create Triggers.
The PostgreSQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
The main concept which is focusing on a join is that, two or more data sets, when joined, combined their columns into a new set of rows, including each of the columns requested from each of the data sets. All joins are standing on the foundation of Cartesian product. The Cartesian product is the set of all possible combinations between two data sets. A join creates a set of rows in a temporary table and works on two or more tables, and each table should at least one common field and must maintain a relation between the common fields. Join keeps the structure unchanged of the base tables.
Types of PostgreSQL JOIN
Left Outer Join
Right Outer Join
Full Outer Join
A PostgreSQL trigger is a function invoked automatically whenever an event associated with a table occurs. An event could be any of the following: INSERT, UPDATE, DELETE or TRUNCATE.
A trigger is a special user-defined function that binds to a table. To create a new trigger, you must define a trigger function first, and then bind this trigger function to a table. The difference between a trigger and a user-defined function is that a trigger is automatically invoked when an event occurs.
PostgreSQL provides two main types of triggers: row and statement level triggers. The differences between the two are how many times the trigger is invoked and at what time. For example, if you issue an UPDATE statement that affects 20 rows, the row level trigger will be invoked 20 times, while the statement level trigger will be invoked 1 time.
You can specify whether the trigger is invoked before or after an event. If the trigger is invoked before an event, it can skip the operation for the current row or even change the row being updated or inserted. In case the trigger is invoked after the event, all changes are available to the trigger.
We will create and manage a basic trigger in this course.