Triggers in SQL and What is DML Trigger
Triggers are actually event based Stored Procedure. It is used to perform some actions on database events. Trigger is mainly used to maintain the history of the table.
There are three types of trigger in the SQL
- DML Trigger
- DDL Trigger
- Logon Trigger
Today we will discuss only DML Trigger as it is mostly used. DML Trigger is used in DML related queries like Insert, Update and Delete.
If you write a trigger for insert on table and when this trigger fires it creates INSERTED table in memory, and then we use that table to manipulate the target table.
Similarly, it is for delete and update triggers as well.
Syntax:-
Following is the syntax to create Trigger in SQL server.
Now we will create Trigger on student table and to maintain the history of the table we will create copy of the student table as history table. After inserting in the student table, trigger will run and it will insert the inserted row in the history table.
Example:-
In the above example we are creating trigger named as insertStudent on tb_student table on Insert event. Then inside the trigger we are just taking the values from INSERTED table and inserting them into tb_student_history table.
In the above code we are just inserting one row in tb_student table and after inserting it will run insertStudent trigger.
In above code we are just executing the select statement to check the data inserted in tb_student is available in tb_student_history or not. Below is the output of the above query.
As you can see in the above image, trigger has inserted the record correctly into tb_student_history table.
Thank you for reading please comment your suggestions, share the article, follow me and Abhima Database Technology publication.
Bhagavad Gita Verse of the Day
अहं वैश्वानरो भूत्वा प्राणिनां देहमाश्रित: |
प्राणापानसमायुक्त: पचाम्यन्नं चतुर्विधम् || 14||
ahaṁ vaiśhvānaro bhūtvā prāṇināṁ deham āśhritaḥ
prāṇāpāna-samāyuktaḥ pachāmy annaṁ chatur-vidham
BG 15.14: It is I who take the form of the fire of digestion in the stomachs of all living beings, and combine with the incoming and outgoing breaths, to digest and assimilate the four kinds of foods.
