Hey, folks! In this article, we will be focusing on SQL Triggers in detail.
Table of Contents
What is a SQL Trigger?
SQL Trigger is a stored procedure/function that gets executed on the encounter of certain actions in the database.
Triggers can be considered as a piece of code or a function that gets triggered or executed upon certain events occuring in the database values.
Need of SQL Trigger
Let us understand the Need of a SQL Trigger with the help of an example.
Consider School/College management. Usually, whenever the teachers arrange any kind of Parent-Teachers meet, they send an email to the registered email address of the parents from the database. It is a tedious process to send invitation emails to all the candidates.
Now, if we replace the entire process with Triggers, that is, we write a stored procedure, it will send a mail on the entry of a new record in the database upon some specific event.
This saves a lot of time and leads to reusability of the code.
Syntax of SQL Trigger
As mentioned above, SQL Trigger is a sequence of events that leads to an definite output value depending upon the pre-defined action mentioned.
Create Trigger Trigger-Name (Before | After) [ Insert | Update | Delete] on [Table_Name] [ for each row | for each column ] [Trigger-body ]
Create Trigger: This statement creates a Trigger for an action to be triggered at specific interval of time.
Before: If we mentioned ‘before’ in the source code, the Trigger executes itself and updates the values before performing the DML operations specified.
After: If we mentioned ‘before’ in the source code, the Trigger executes itself and updates the values after performing the DML operations specified.
[ Insert | Update | Delete ]: These are the DML operations that can be used alongside the Trigger.
[ for each row | for each column ]: The triggers executes itself either row-wise or column-wise.
Trigger-body: The code to be executed of the trigger is mentioned within this block.
Implementing SQL Trigger through examples
Having understood the Working and Syntax of SQL Triggers, let us now dive into the implementation of the same in a stepwise manner.
Initially, we create a SQL table using SQL Create query with the columns as follows:
- Price(per item)
- Quantity(per item)
create table Info(item_id integer, Price integer, Quantity integer, Total_Cost integer);
Now, we create a SQL Trigger for the Table — ‘Info’ created above.
CREATE TRIGGER Get_value Before INSERT ON Info FOR EACH ROW SET new.Total_Cost = new.Price*new.Quantity;
We have created a Trigger ‘Get_value’ as a BEFORE INSERT Trigger i.e. the Trigger would execute itself and update the data values before the data gets saved to the database.
After the creation of the Trigger, we perform DML – INSERT operation to insert the data into the table.
Note: As we want to calculate the Total_Cost, we have entered zero in its place during INSERT query execution.
Having inserted the records, the Trigger performs the execution of its body. And replaces the zeros of Total_cst column by their calculated values.
insert into Info(item_id, Price,Quantity,Total_Cost) values(1, 150,30,0); insert into Info(item_id, Price,Quantity,Total_Cost) values(2, 100,23,0); insert into Info(item_id, Price,Quantity,Total_Cost) values(3, 250,5,0);
Finally, we display the updated data values using SQL SELECT command.
select * from Info;
Advantages of SQL Trigger
- SQL Trigger easily identifies changes made in the database.
- It maintains the integrity of the data.
Limitations of SQL Trigger
The most important and probably the only disadvantage of Triggers is the problem of Troubleshooting.
Triggers executes automatically in the database, which makes it difficult to troubleshoot and resolve.
By this, we have come to the end of this topic. Please feel free to comment below, in case you come across any doubt.
For more such posts related to SQL, please do visit SQL JournalDev.