SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

Trigger in SQL

In this article, we will learn about the concept of trigger in SQL and its implementation with the help of an example.

A Trigger in Structured Query Language is a set of procedural statements executed automatically when there is any feedback to certain events on the table in the database. Triggers are used to protect the integrity of the data in the database.

Let’s see the following situation to understand the concept of trigger in SQL:  

Suppose Supriya is the information technology manager in a multinational company. When the record of a new employee is entered into the database, she has to send the 'Congratulations' message to each new employee. If there are five or six employees, Supriya can do it manually, but if the number of new Employees is more than the thousand, then in such a condition, she has to use the trigger in the database.

Thus, Supriya has to create the trigger in the table, which will automatically send a 'Congratulations' message to the new employees once their record is inserted into the database.

The trigger is always executed with the specific table in the database. If we remove the table, all the triggers associated with that table are also deleted automatically.

Triggers in the Structured Query Language are called only before the events have occurred or after the events have occurred.

Only three events occur in the trigger before or after the events are the Insert events, the Update events, or the Delete events.

1. INSERT Event: Insert event occurs whenever a new record is inserted into the table.

2. UPDATE Event: An update event occurs whenever the existing records are updated in the table.

3. DELETE Event: The Delete event occurs whenever the record is deleted from the table.

Types of triggers in the Structured Query Language

Following are the six types of triggers used in the Structured Query Language:

1. AFTER INSERT Trigger:

The after insert trigger is invoked in SQL after the records are inserted in the table.

2. AFTER UPDATE Trigger:

The after update trigger is invoked in SQL after the existing records in the table are updated.

3. AFTER DELETE Trigger:

The after delete trigger is invoked in SQL after the record are deleted from the table.

4. BEFORE INSERT Trigger:

The before insert trigger is invoked in SQL before the records are inserted in the table.

5. BEFORE UPDATE Trigger:

The before update trigger is invoked in SQL before the existing records in the table are updated.

6. BEFORE DELETE Trigger:

The before delete trigger is invoked in SQL before the record are deleted from the table.

Syntax of trigger in SQL:

CREATE TRIGGER Trigger_Name  

[BEFORE | AFTER]  [Insert | Update | Delete] 

ON [Table_Name] 

[FOR EACH ROW | FOR EACH COLUMN] 

AS 

Set of SQL Statement
  • In the trigger syntax, we have to define the trigger name just after the CREATE TRIGGER keyword in the syntax.
  • After the trigger name, we have to define which trigger we want to invoke, the before trigger or the after trigger keyword with the event name.
  • Then, we have to mention the table name on which trigger is occurred.
  • After the table name, we must define the trigger on the row-level or the column-level trigger.
  • And in the end, we have to write the SQL statement that acts on the events occurring.

Example of trigger in SQL

To understand the concept of trigger in the SQL, we have to create a table on which trigger is to be executed.

The following query creates the Student table in the SQL database.

CREATE TABLE Student(

Student_rollno INT NOT NULL PRIMARY KEY,

FirstName varchar(40),

English_Marks INT,

Physics_Marks INT,

Chemistry_Marks INT,

Biology_Marks INT,

Maths_Marks INT,

Total_Marks INT,

Percentage INT);

The following query shows the structure of the Student table:

DESC Student;

FieldTypeNULLKeyDefaultExtra
Student_RollNoINTNOPRINULL
First_NameVarchar(40)YESNULL
English_MarksINTYESNULL
Physics_MarksINTYESNULL
Chemistry_MarksINTYESNULL
Biology_MarksINTYESNULL
Maths_MarksINTYESNULL
Total_MarksINTYESNULL
PercentageINTYESNULL

The following query fires the trigger before the insertion of the student record in the table:

CREATE TRIGGER Student _Marks  

BEFORE INSERT 

ON 

Student 

FOR EACH ROW 

SET new.Total_Marks = new.English_Marks + new.Physics_Marks + new.Chemistry_Marks + new.Biology_Marks + new.Maths_Marks,   

new.Percentage = ( new.Total_Marks / 500) * 100;

The following queries insert the record into the Student table.

INSERT INTO Student (Student_RollNo, First_Name, English_Marks, Physics_Marks, Chemistry_Marks,Biology_Marks, Maths_Marks, Total_Marks, Percentage) VALUES ( 1, ‘Raman’, 90, 80, 75, 70 ,92, 0, 0);  

To check the output of the above INSERT statement, you have to type the following SELECT statement:

SELECT * FROM Student

Output:

Student_RollNoFirst_NameEnglish_Marks  Physics_Marks  Chemistry_Marks  Biology_Marks  Maths_Marks  Total_Marks  Percentage
1Raman908075709240781

Advantages of Triggers in SQL

Following are the three main advantages of triggers in Structured Query Language:

  1. SQL provides an alternate way to maintain the data and referential integrity in the tables.
  2. Triggers help in executing the scheduled tasks automatically.
  3. They catch the errors in the database layer of various businesses.
  4. They allow the database users to validate values before inserting and updating.

Disadvantages of Triggers in SQL

Following are the main disadvantages of triggers in Structured Query Language:

  1. They are not compiled.
  2. It is not possible to find and debug the errors in triggers.
  3. If we use the complex code in the trigger, it makes the application run slower.
  4. Trigger increases the high load on the database system.



ADVERTISEMENT
ADVERTISEMENT