Microsoft SQL Server Certification Course
- 1k Enrolled Learners
- Live Class
A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance. In this article, you will learn how to implement triggers along with examples.
The following topics will be covered in this article:
Triggers are the SQL codes that are automatically executed in response to certain events on a particular table. These are used to maintain the integrity of the data. A trigger in SQL works similar to a real-world trigger. For example, when the gun trigger is pulled a bullet is fired. We all know this, but how this is related to Triggers in SQL? To understand this let’s consider a hypothetical situation.
John is the marketing officer in a company. When a new customer data is entered into the company’s database he has to send the welcome message to each new customer. If it is one or two customers John can do it manually, but what if the count is more than a thousand? Well in such scenario triggers come in handy.
Thus, now John can easily create a trigger which will automatically send a welcome email to the new customers once their data is entered into the database. So I hope you are clear with the introduction of Triggers in SQL.
Always remember that there cannot be two triggers with similar action time and event for one table. For example, we cannot have two BEFORE UPDATE triggers for a table. But we can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.
Syntax and Example
Lets now look at the syntax of a trigger.
Create Trigger Trigger_Name (Before | After) [ Insert | Update | Delete] on [Table_Name] [ for each row | for each column ] [ trigger_body ]
Now let me break down this syntax and explain each and every part in detail.
So this was all about a simple trigger. But we can also create a nested trigger that can do multi-process. Also handling it and terminating it at the right time is very important. If we don’t end the trigger properly it may lead to an infinite loop.
You might be wondering in which scenario we can use the nested trigger. Rather than giving you a tailored answer let me share a scenario with you, which will help you in understanding the nested trigger in a better way. Continuing from the earlier scenario, John sent an email for every new customer that was added to the company’s database. Now, what if he wishes to keep track of the number of customers to whom the email was sent? Now John needs to create a nested trigger to keep the track of the count along with sending an email.
So that was all about the syntax of triggers, lets now try to implement an example of triggers in SQL.
Example for Trigger:
In the below trigger, we are trying to calculate the percentage of the student as soon as his details are updated to the database.
CREATE TRIGGER sample_trigger before INSERT ON student FOR EACH ROW SET new.total = new.marks/6;
Here the “NEW” keyword refers to the row that is getting affected.
We can perform many operations using triggers. Some may be simple and some may be a little complex, but once if we go through the query its easy to understand.
DROP TRIGGER trigger name;
The below code will display all the triggers that are present.
The below code will display all the triggers that are present in a particular database.
SHOW TRIGGERS IN database_name;
SHOW TRIGGERS IN edureka;
In the above example, all the triggers that are present in the database named Edureka will be displayed.
We also look at some major variants of the triggers that is Before insert and After insert. We have already seen a trigger in the example. But with the help of the table lets see how exactly this works.
As we have already understood how to create a trigger, now let’s understand the two variants of the trigger those are Before insert and After insert. in order to implement them, let’s create a student table with various columns as shown below:
CREATE TABLE Student( studentID INT NOT NULL AUTO_INCREMENT, FName VARCHAR(20), LName VARCHAR(20), Address VARCHAR(30), City VARCHAR(15), Marks INT, PRIMARY KEY(studentID) );
Now if we execute this query we get the following table.
Let’s try to use the first variant i.e, Before Insert
CREATE TRIGGER calculate before INSERT ON student FOR EACH ROW SET new.marks = new.marks+100;
Here when we insert data into the student table automatically the trigger will be invoked. The trigger will add 100 to the marks column into the student column.
Now let’s use the second variant i.e, After Insert
To use this variant we need one more table i.e, Percentage where the trigger will store the results. Use the below code to create the Percentage Table.
create table Final_mark( per int );
Now let us use the after insert trigger
CREATE TRIGGER total_mark after insert ON student FOR EACH ROW insert into Final_mark values(new.marks);
Here when we insert data to the table, total_mark trigger will store the result in the Final_mark table.
This brings us to the end of this Triggers in SQL article. I hope you understood the concepts of Triggers.
If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this Triggers in SQL and we will get back to you.