Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What Are Triggers In SQL? 
3.
How can we use triggers in PostgreSQL?
3.1.
Syntax
3.2.
Create Trigger
4.
Operations On Triggers
4.1.
Listing Triggers
4.2.
Dropping Triggers
4.3.
Renaming Triggers
4.4.
Disabling Triggers
5.
Frequently Asked Questions
5.1.
What is the advantage of using Triggers in SQL?
5.2.
How does a trigger access elements of the associated row?
5.3.
Write one disadvantage of using Triggers in SQL.
6.
Conclusion
Last Updated: Mar 27, 2024
Medium

Working With Triggers In PostgreSQL

Author Lokesh Sharma
1 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

“The sheriff pulled the trigger and fired a bullet to defeat the criminal.” 

Well, the sheriff might be the hero here, but the trigger played an equally important role. Similarly, triggers play a crucial role in using databases and help run queries automatically. In this article, we understand the working of triggers and consider examples to use them. 

Let us start by understanding triggers in general.

working with triggers in postgresql

What Are Triggers In SQL? 

A trigger is a stored piece of code in the database that runs automatically whenever a specific event occurs. Consider it similar to the trigger of a gun. An event activates the trigger, and then the trigger executes a function: firing the bullet. 

We can define a PSQL trigger for the following cases.

  1. Before an operation is performed on a row. We can insert, update or delete data after checking the before constraints.
     
  2. After an operation is performed on a row. We can insert, update or delete data after checking the after constraints.
     
  3. Some triggers are specified to be fired Instead Of the trigger event. We can only use these triggers on views. 
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

How can we use triggers in PostgreSQL?

Let us see how to create and use triggers. 

To create a trigger, we first need to make a trigger function. When invoked, our trigger will call this function. This function is user-defined and does not accept any arguments. It only returns a value of the type “trigger”. We can create such functions using any language supported by PSGL. 

Syntax

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS 
$$
BEGIN
   -- code (trigger logic)
END;
$$

Create Trigger

After creating the trigger function that implements the logic of the trigger, we will make its trigger. Any PSGL trigger is created using the Create Trigger statement. Take a look at the syntax.


CREATE TRIGGER <trigger_name> 
   {BEFORE | AFTER | INSTEAD OF} {event_name}
   ON <table_name>
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE <trigger_function>

 

Let us understand the above syntax. 

  1. The <trigger_name> is the name of the trigger to be created. It is always specified after the Create Trigger statement.
     
  2. After naming the trigger, we specify the event for which the trigger will be fired. But before that, we state whether the trigger will activate BeforeAfter, or Instead Of the event.
     
  3. The event, in this case, can be of 4 types, Insert, Delete, Update, or Truncate.
     
  4. The <table_name> is the table's name for which we create the trigger. The trigger is linked to its table, so if a table gets erased, so does the trigger.
     
  5. At last, we specify whether the trigger will work for each row or statement. 

 

Let us consider a simple example. Suppose Coding Ninjas is organizing its weekly coding contest. Student X is solving the list of questions one by one. The organizers at coding ninjas want to maintain the contest information. One table should store all the questions solved by ninja X along with the marks scored. However, they also want to keep the time in a separate table at which each question is submitted. 

We can take the help of triggers to solve the problem. We first create the questions table and fill in some entries. After filling in the entries, the table looks like this.

Code

create table questions (
    id INT NOT NULL,
    score varchar(3) NOT NULL
);
questions table

We then create a submission_time table which will store the submission time of each question. 

Code:

create table submission_time (
    questn_id INT NOT NULL,
    submit_time text NOT NULL
);

 

We will now code the record_time() function, which will act as the trigger function.

Code

create function record_time() 
returns trigger AS 
$$
   begin
      insert into submission_time(questn_id, submit_time) values (new.ID, current_timestamp);
      return new;
   end;
$$ language plpgsql;

 

The above function, when run, will add a new entry to the table submission_time. We run this function using the time_trigger

Code: 

create trigger time_trigger after insert on questions
for each row execute procedure record_time();

 

Our trigger is complete. Whenever we create a new entry in the questions table, the trigger will automatically get fired and add a new row to the submission_time table containing the submission information of the question.

insert into questions
values (4, 400);

 

Now, if you view the submission_time table, you can see the new row added.

output image

Operations On Triggers

Let us learn about some basic operations on triggers. These operations enable us to use triggers efficiently.

Listing Triggers

In the previous sections, we learned about creating triggers and using them. All the triggers that you create are stored in the pg_trigger table. You can list down all the triggers present in the database from this table. 

Just type select * from pg_trigger;

trigger list image

Dropping Triggers

You can remove or delete an existing trigger from the database. You just need to run the following statement.

drop trigger <trigger_name>;

 

Type the name of the trigger you want to delete in the <trigger_name> space.

Renaming Triggers

You may want to rename your existing trigger without changing its definition. You can easily do it using the Alter and Rename keywords.

Syntax

Alter trigger <old_trigger_name> on <table_name> rename to <new_name>;

Disabling Triggers

Sometimes, it may happen that you do not want to use a trigger for now but may need it in the future. So, instead of deleting it from the database, you can just disable it. 

ALTER TABLE table_name
DISABLE TRIGGER trigger_name | ALL

 

Use ALL instead of the trigger name if you want to disable all the triggers in the database.

To enable the trigger again, replace the disable keyword with ENABLE keyword in the above syntax. 

Frequently Asked Questions

What is the advantage of using Triggers in SQL?

Triggers help in the rapid development of applications along with easy maintenance. We do not have to code the database's functionality repeatedly.

How does a trigger access elements of the associated row?

The trigger can access the row elements inserted, updated, or deleted using the new and old keywords. New.column_name and Old.column_name can be used to access a specific row column.

Write one disadvantage of using Triggers in SQL.

Triggers can slow down the performance of the application. The whole trigger code runs everytime an insert / delete / update operation is performed. This decreases the performance.

Conclusion

This blog discussed triggers in PostgreSQL. We learned how to create and use triggers, along with examples. Having the knowledge about creating and using triggers will increase your work efficiency in the database

We hope you enjoyed reading this article. If you wish to learn more about SQL, refer to the following blogs. 

 

Visit our website to read more such blogs. Make sure you enroll in our other courses as well. You can take mock testssolve problems, and interview puzzles. Also, you can check out some exciting interview stuff- interview experiences and an interview bundle for placement preparations. Do upvote our blog to help fellow ninjas grow.

Keep Grinding! 🦾

Happy Coding! 💻

Live masterclass