Hello. In this tutorial, we will learn the SQL triggers and see a practical implementation of them in the postgresql database.
You can also check this tutorial in the following video:
1. Introduction
SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –
- Parsing – Process to check the SQL query syntax
- Binding – Process to check the SQL query semantics
- Optimization – Process to generate the SQL query execution plan
1.1 Usage of SQL
Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –
- Execute queries against the database
- Retrieve data from the database
- Insert new records into the database
- Update existing records into the database
- Created stored procedures, functions, and materialized views in the database
- Create users and grant permissions
- Set permissions on tables, stored procedures, functions, and materialized views
1.2 SQL Triggers
Triggers in the postgresql are the database callback functions that are automatically performed/invoked when a specific database event occurs. A trigger marked for each row is invoked once for every row that the sql operation modifies while a trigger marked for each statement only executes once for any given operation regardless of how many rows are modified. A trigger can be executed –
- Before the operation is attempted on a row
- After the operation is completed in a row
To create a new trigger in postgresql we follow the below steps –
- First, create a trigger function using the
create functionsql statement - Second, bind the trigger function to a table using the
create triggerstatement
A trigger in postgresql is represented by the following syntax where the create trigger statement help to create a new trigger –
Trigger syntax
create trigger trigger_name [before|after|instead of] event_name on table_name [ ...trigger logic goes here... ];
Make note that a trigger is automatically dropped when the associated table is dropped. The before, after, and instead of keyword determines when the trigger actions will be executed i.e. insert, update, or removal of the row.
2. SQL Triggers
Let us dive into some practice implementation on the postgresql database.
2.1 Pre-requirement – Postgres Setup
Usually, setting up the database is a tedious step but with technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the postgresql up and running on the local machine.
Docker commands
-- command to run postgres on docker -- -- remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the pgAdmin gui tool for connecting to the server.
2.2 Creating Tables
To implement this tutorial I will be creating two tables named – product and audit in the practice database. Here the audit table is responsible to log change the changes whenever a new record is added to the product table. You’re free to execute the below SQL in the database of your choice.
Create tables
-- required for auto generation of uuid -- one time activity create extension if not exists "uuid-ossp"; -- create two tables: product and audit drop table if exists product; drop table if exists audit; -- product table create table product ( id serial not null, pid uuid default uuid_generate_v1(), pname varchar(100) unique, quantity INT default 0, available varchar(100), primary key(id) ); -- audit table create table audit ( id serial not null, pid uuid, entry_date timestamp, primary key(id) );
The product and audit tables will be created as shown below.
2.3 Create a Function and Trigger
First, create the trigger function called audit_log(). The function inserts the product id and the current timestamp of the new record added to the product table into the audit table.
Create function
-- a. create audit function -- trigger function is similar to a user-defined function but it does not take any argument and has a return type of type trigger create or replace function audit_log() returns trigger as $product$ begin insert into audit (pid, entry_date) values (new.pid, current_timestamp); return new; end; $product$ language plpgsql;
Secondly, bind the function to the product table via the trigger called audit_trigger. The trigger function is automatically invoked before the record is added to the product table to log the changes.
Create trigger
-- b. create trigger /* syntax = create trigger trigger_name [before|after|instead of] event_name on table_name [ ...trigger logic goes here... ]; */ create trigger audit_trigger after insert on product for each row execute procedure audit_log();
We can also list the trigger created for the product table with the help of the below sql statement –
List trigger
-- c1. view all triggers on a particular table -- syntax = select tgname from pg_trigger, pg_class where tgrelid=pg_class.oid and relname='table_name'; select tgname from pg_trigger, pg_class where tgrelid=pg_class.oid and relname='product';
2.4 Insert mock data and Validate
Now let us add one record in the product table that should result in creating an audit entry log in the audit table.
Inserting record in the product table
insert into product (pname, quantity, available) values ('Plums', 34, false);
The above insert statement will create one record in the product table and at the same time, one record will also be created in the audit table. This record will be created by the trigger.
For every record inserted in the product table an audit log entry will be created in the audit table. Similarly, if you go on adding mock data to the product table corresponding entries will be created in the audit table.
3. Summary
In this tutorial, we learned the brief to sql triggers and implemented it in the postgresql database. Triggers are used to perform a specific function when a specific event occurs in the database. They are not specific to insert operation triggers but can also be created for update and delete operations based on the requirement. You can download the sql scripts and docker commands used in this tutorial from the Downloads section.
4. Download the Scripts
This was a tutorial on learning the SQL triggers.
You can download the full source code of this example here: SQL Triggers
