_edited_edited.png)
In this blog post, my goal is to guide you through the usage of Postgresql triggers. However, it is crucial to first establish a fundamental understanding of the topic.
what is PostgreSQL?
PostgreSQL is an open-source relational database system. It supports both SQL (relational) and JSON (non-relational) querying.
It is used as a primary database for many web applications as well as mobile and analytics applications.
PostgreSQL supports most popular programming languages: Python, Java so on
Who uses PostgreSQL?
Many companies have built products and solutions based on PostgreSQL. Some featured companies are Apple, Fujitsu, Red Hat, Cisco, Juniper Network, Instagram, etc.
Installation Part
if you are a beginner to start. follow the steps here based on your Operating System.
Step 1: Installing Postgresql on your machine
For windows,
For Mac Os,
Step 2: Connect To a PostgreSQL Database Server
Step 3: Load PostgreSQL with Given Database
Download the given database with the steps as a reference.
Postgresql Basics
CREATE statements allow us to create a new table in the database.
Syntax:
CREATE TABLE table_name ( column1 datatype,column2 datatype, ....);
INSERT INTO command inserts a new row into a table.
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
ALTER statement is used to modify columns.
With ALTER, you can add columns, remove them, or even modify them.
Syntax:
ALTER TABLE table_name
DROP/ ALTER Column column_name;
ALTER TABLE table_name
ADD Column column_name Datatype;
UPDATE statement is used to modify rows.
However, UPDATE can only update a row, and cannot remove or add rows.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
SELECT statements are used to fetch data from a database. It always returns a new table called the result set.
Syntax:
SELECT column1, column2
FROM table_name;
* is a special wildcard character that allows one to select every column in a table without having to name each one individually.
Syntax: SELECT * FROM table_name;
TRUNCATE TABLE command deletes the data inside a table, but not the table.
Syntax:
TRUNCATE TABLE table_name;
DELETE FROM statement deletes one or more rows from a table by deleting existing records.
Syntax:
DELETE FROM table_name WHERE condition;
DROP TABLE statement is used to drop an existing table in a database.
Syntax:
DROP TABLE table_name;
Note: 'AS ' renames a column or table.
Postgresql Triggers
PostgreSQL trigger is a function invoked automatically whenever an event such as insert, update, or delete occurs.
PostgreSQL provides two main types of triggers:
Row-level triggers
Statement-level triggers.
The differences between the two kinds are how many times the trigger is invoked and at what time.
For example, if we use an UPDATE statement that modifies 10 rows, the row-level trigger will be invoked 10 times, while the statement-level trigger will be invoked 1 time.
Triggers are useful in various applications to access the database and to keep the cross-functionality within the database that runs automatically whenever the data of the table is modified.
We can use triggers to maintain complex data integrity rules which cannot implement elsewhere except at the database level.
The main drawback of using a trigger is that you must know the trigger exists and understand its logic to figure out the effects when data changes.
Things to be noted to create the trigger:
Create a trigger function using CREATE FUNCTION statement.
Trigger function does not take any arguments and has a return value with the type trigger.
A trigger function receives data about its calling environment through a special structure called TriggerData which contains a set of local variables.
For example, OLD and NEW represent the states of the row in the table before or after the triggering event.
We can create a trigger function using any language supported by PostgreSQL. Here, we will use PL/pgSQL.
Syntax:
CREATE FUNCTION trigger_function()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
-- trigger logic
END;
$$
Second, bind the trigger function to a table by using CREATE TRIGGER statement.
Syntax:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} { event }
ON table_name
[FOR [EACH] { ROW | STATEMENT }]
EXECUTE PROCEDURE trigger_function
CREATE TRIGGER statement creates a new trigger.
Specify the name of the trigger after the TRIGGER keywords.
Specify the timing that causes the trigger to fire. It can be BEFORE or AFTER an event occurs.
Specify the event that invokes the trigger. The event can be INSERT, DELETE, UPDATE, or TRUNCATE.
Specify the name of the table associated with the trigger after the ON keyword.
Specify the type of triggers which can be:
A row-level trigger that is specified by the FOR EACH ROW clause.
A statement-level trigger that is specified by the FOR EACH STATEMENT clause.
Then, Specify the name of the trigger function after the EXECUTE PROCEDURE keywords.
Delete the trigger:
Syntax:
DROP TRIGGER [IF EXISTS] trigger_name
ON table_name [ CASCADE | RESTRICT ];
First, mention the trigger name we want to delete after the DROP TRIGGER keywords.
Use IF EXISTS to conditionally delete the trigger only if it exists.
Specify the name of the table to which the trigger belongs.
Use the CASCADE option to delete objects that depend on the trigger.
Use the RESTRICT option to refuse to drop the trigger if any objects depend on it.
By default, the DROP TRIGGER statement uses RESTRICT.
Alter the trigger:
Syntax:
ALTER TRIGGER trigger_name
ON table_name
RENAME TO new_trigger_name;
Note: PostgreSQL doesn’t support the OR REPLACE statement that allows you to modify the trigger definition like the function that will be executed when the trigger is fired.
Hope it helps!
Happy Learning!!