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

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

• What is a Trigger?
• Advantages and Disadvantages of Triggers
• Operations on Triggers

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.