Triggers in SQL

4 mins read342 Views Comment
clickHere
Updated on Apr 29, 2022 13:59 IST

With databases storing extensive amounts of data, database performance is a major concern that most professionals are often worried about.

2022_04_Triggers.jpg

Triggers in SQL are small procedural codes that create an impact on the database performance by executing in response to an event. In this article, on Triggers in SQL, I will give you a detailed walkthrough on how to create and use triggers.

Contents

In this article, the following table is considered to explain the operators in SQL.

Patients Table

Patient ID Patient Name Sex Age Address Postal Code State Country Hemoglobin
01 Sheela F 23 Flat no 201, Vasavi Heights, Yakutapura  500023 Telangana India 82.321
02 Rehan M 21 Building no 2, Yelahanka 560063 Karnataka India 83.231
03 Anay M 56 H No 1, Panipat 132140 Haryana India 94.567
04 Mahira F 42 House no 12, Gandhinagar 382421 Gujarat India 78.567
05 Nishant M 12 Sunflower Heights, Thane 400080 Maharashtra India 65.234

What is a Trigger?

Triggers are a set of SQL statements invoked automatically in response to an event. Every trigger, associated with a table activates by DML commands such as INSERT, UPDATE, and DELETE. They use to maintain the integrity of data.

The following are two types of triggers:

  • Row-Level Trigger: Activates for each row, affected by INSERT, UPDATE or DELETE commands.
  • Statement-Level Trigger: Activates for each of the events which occur, irrespective of any of the DML commands.

One thing you must remember is that terminating the trigger at the right time is very important, else it will lead to an infinite loop. You can also create nested triggers to initiate multiple processes.

Note: 

We cannot use the same triggers at a particular instance of an event. For example, we cannot have two AFTER INSERT triggers, but you can use an AFTER INSERT and BEFORE UPDATE triggers

Syntax:

CREATE TRIGGER TriggerName    
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON TableName FOR EACH ROW    
         BEGIN    
        --Declarations    
        --Trigger Code    
        END;
  • CREATE TRIGGER –  Used to declare the trigger block
  • TRIGGERNAME – Used to mention the trigger name, and must be unique for each trigger.
  • BEFORE | AFTER – Used to specify when the triggers must be executed. It could be before an event or after an event.
  • BEFORE TRIGGER – This type of trigger is used to validate or update a record before saving them to the database.
  • AFTER TRIGGER – This type of trigger is used to access values set by the system and record the changes. Here, remember that the AFTER TRIGGER cannot be used to update a record.
  •  INSERT | UPDATE | DELETE  – Used to indicate the data manipulation operation.
  • ON TABLE NAME – Used to indicate the table on which the trigger has to be applied
  • FOR EACH ROW | FOR EACH COLUMN – These are two types of triggers that can be used to indicate if the trigger has to be executed for every row or column.
  • TRIGGER BODY – Indicates the queries that are executed when a trigger is called. 

Example:

CREATE TRIGGER PatientTrigger   
AFTER INSERT 
ON Patients FOR EACH ROW    
BEGIN    
SET NEW.AvgHemoglobin = NEW.Hemoglobin/5
END;

Note: The new keyword is used to indicate the row that should be affected.

Next in this article let us understand the advantages and disadvantages of using Triggers.

Advantages and Disadvantages of Triggers

Advantages

  • Provides security to the tables in a database
  • Handles errors in the database layer and allows users to maintain data integrity
  • Allows users to keep a track of data changes in the table
  • Supports scheduling of events to be invoked automatically on the occurrence of events.

Disadvantages

  • Increase the overhead of the database
  • Does not support all validations 
  • Tough to troubleshoot as triggers execute automatically

Finally, let us understand the various operations you can perform with triggers.

Operations on Triggers

You can perform the following operations on triggers:

  • Before Insert: Activated before the data is inserted into the table.
  • After Insert: Activates after the data is inserted into the table.
  • Before Delete: Activated before the removal of data from the table.
  • After Delete: Activates after the removal of data from the table.
  • Before Update: Activated before the data being updated in the table.
  • After Update: Activates after the data updates in the table.

Apart from the above mentioned actions you can also drop and display a trigger.

DISPLAY A TRIGGER

Used to display all the triggers invoked till date.

Syntax:

#To display ALL the triggers present
SHOW TRIGGERS;
 
#To display ALL the triggers present IN a DATABASE
SHOW TRIGGERS IN DatabaseName;

Example:

SHOW TRIGGERS;
 
SHOW TRIGGERS IN Patients;

DROP A TRIGGER

Used to delete a trigger.

Syntax:

DROP TRIGGER TriggerName;

Example:

DROP TRIGGER PatientTrigger;

With this, we end this article on SQL Triggers. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio