sqlsql

SQL Triggers

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:

SQL Triggers Tutorial – 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 function sql statement
  • Second, bind the trigger function to a table using the create trigger statement

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.

Fig. 1. Postgres on Docker

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.

Fig. 2: Tables created successfully

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;
Fig. 3: Trigger function created successfully

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();
Fig. 4: Trigger created successfully

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';
Fig. 5: List the trigger

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.

Fig. 6: Audit entry log in the audit table

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.

Download
You can download the full source code of this example here: SQL Triggers

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button