Working With Triggers In PostgreSQL

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 Before, After, 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
);

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.

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;

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. 

• introduction to SQL..
• advance sql.
• pl-sql introduction..

 

Visit our website to read more such blogs. Make sure you enroll in our other courses as well. You can take mock tests, solve 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! 💻