Triggers In SQL Server
Trigger:-
A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.
Types of Trigger:-
There are three types of triggers which are following.
1. DML triggers:-DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers are fired, when ever data is modified using INSERT, UPDATE, and DELETE events.
DML triggers can be again classified into 2 types
A. After triggers (Sometimes called as FOR triggers):- After triggers, as the name says, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution.
B. Instead of triggers:- As the name says, INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, can cause an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution.
Now, we are using SQL SERVER. So, first we create two tables named are tblEmployee and tblEmployeeAuditRecord .
When we insert a new Employee in tblEmployee table, we want to capture the ID and the date and time, the new employee is added in tblEmployeeAuditRecord table. The easiest way to achieve this, is by having an AFTER TRIGGER for INSERT event.
AFTER TRIGGER for INSERT event on tblEmployee table:-
In the trigger, we are getting the Id from inserted table. So, what is this inserted table? INSERTED table, is a special table used by DML triggers. When you add a new row into tblEmployee table, a copy of the row will also be made into inserted table, which only a trigger can access. You cannot access this table outside the context of the trigger. The structure of the inserted table will be identical to the structure of tblEmployee table.
So, now if we execute the following INSERT statement on tblEmployee. Immediately, after inserting the row into tblEmployee table, the trigger gets fired (executed automatically), and a row into tblEmployeeAuditRecord, is also inserted.
Now, we want to capture audit information, when a row is deleted from the table, tblEmployee.
Example:- AFTER TRIGGER for DELETE event on tblEmployee table:-
The only difference here is that, we are specifying, the triggering event as DELETE and retrieving the deleted row ID from DELETED table. DELETED table, is a special table used by DML triggers. When you delete a row from tblEmployee table, a copy of the deleted row will be made available in DELETED table, which only a trigger can access. Just like INSERTED table, DELETED table cannot be accessed, outside the context of the trigger and, the structure of the DELETED table will be identical to the structure of tblEmployee table.
Instead of insert trigger:-
INSTEAD OF triggers are fired instead of the triggering event(INSERT, UPDATE or DELETE events). INSTEAD OF triggers are usually used to correctly update views that are based on multiple tables.
Now, we are using SQL Server. So, first of all we create two tables named are tblStudent and tblBranch and insert some records as shown in below.
tblStudent
tblBranch
Since, we now have the required tables, let's create a view based on these tables. The view should return Employee Id, Name, Email, Gender and BranchName columns. So, the view is obviously based on multiple tables.
Create a view for above requirement:-
Now, we try to insert record into view vwEmployeeRecord. It witll be give following errors.
So, inserting a row into a view that is based on multipe tables, raises an error by default. Now, let's understand, how INSTEAD OF TRIGGERS can help us in this situation. Since, we are getting an error, when we are trying to insert a row into the view, let's create an INSTEAD OF INSERT trigger on the view vwEmployeeRecord.
Create INSTEAD OF INSERT trigger:
A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.
Types of Trigger:-
There are three types of triggers which are following.
- DML triggers
- DDL triggers
- Logon trigger
1. DML triggers:-DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers are fired, when ever data is modified using INSERT, UPDATE, and DELETE events.
DML triggers can be again classified into 2 types
A. After triggers (Sometimes called as FOR triggers):- After triggers, as the name says, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution.
B. Instead of triggers:- As the name says, INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, can cause an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution.
Now, we are using SQL SERVER. So, first we create two tables named are tblEmployee and tblEmployeeAuditRecord .
--------------->>tblEmployee<<------------------- CREATE TABLE tblEmployee ( Id int Primary Key, Name nvarchar(30), Email nvarchar(200), Salary int, Gender nvarchar(10), DepartmentId int ) --------------->>tblEmployeeAuditRecord<<------------------- CREATE TABLE tblEmployeeAuditRecord ( Id int identity(1,1) primary key, AuditData nvarchar(1000) )
When we insert a new Employee in tblEmployee table, we want to capture the ID and the date and time, the new employee is added in tblEmployeeAuditRecord table. The easiest way to achieve this, is by having an AFTER TRIGGER for INSERT event.
AFTER TRIGGER for INSERT event on tblEmployee table:-
CREATE TRIGGER tr_tblEmployeeForInsert ON tblEmployee FOR INSERT AS BEGIN Declare @Id int Select @Id = Id from inserted insert into tblEmployeeAuditRecord -----Insert the record into the 'tblEmployeeAuditRecord' table values('New Employee with ID = ' + Cast(@Id as nvarchar(5)) + ' Is Added at ' + cast(Getdate() as nvarchar(20))) END
In the trigger, we are getting the Id from inserted table. So, what is this inserted table? INSERTED table, is a special table used by DML triggers. When you add a new row into tblEmployee table, a copy of the row will also be made into inserted table, which only a trigger can access. You cannot access this table outside the context of the trigger. The structure of the inserted table will be identical to the structure of tblEmployee table.
So, now if we execute the following INSERT statement on tblEmployee. Immediately, after inserting the row into tblEmployee table, the trigger gets fired (executed automatically), and a row into tblEmployeeAuditRecord, is also inserted.
INSERT INTO tblEmployee VALUES(1001,'Santosh kumar singh','s@gmail.com',1200,'Male',1)
Now, we want to capture audit information, when a row is deleted from the table, tblEmployee.
Example:- AFTER TRIGGER for DELETE event on tblEmployee table:-
CREATE TRIGGER tr_tblEmployeeForDeleteRecord ON tblEmployee FOR DELETE AS BEGIN Declare @Id int Select @Id = Id from deleted insert into tblEmployeeAuditRecord values('An Existing Employee with ID = ' + Cast(@Id as nvarchar(5)) + ' Is Deleted at ' + Cast(Getdate() as nvarchar(20))) END
The only difference here is that, we are specifying, the triggering event as DELETE and retrieving the deleted row ID from DELETED table. DELETED table, is a special table used by DML triggers. When you delete a row from tblEmployee table, a copy of the deleted row will be made available in DELETED table, which only a trigger can access. Just like INSERTED table, DELETED table cannot be accessed, outside the context of the trigger and, the structure of the DELETED table will be identical to the structure of tblEmployee table.
Instead of insert trigger:-
INSTEAD OF triggers are fired instead of the triggering event(INSERT, UPDATE or DELETE events). INSTEAD OF triggers are usually used to correctly update views that are based on multiple tables.
Now, we are using SQL Server. So, first of all we create two tables named are tblStudent and tblBranch and insert some records as shown in below.
-----------tblStudent-------------- CREATE TABLE tblStudent ( Id int Primary Key, Name nvarchar(30), Email nvarchar(200), Gender nvarchar(10), BranchId int ) -----------tblBranch-------------- CREATE TABLE tblBranch ( BranchId int Primary Key, BranchName nvarchar(20) ) ----------- Insert records into tblStudent-------------- INSERT INTO tblStudent VALUES(1001,'Santosh','s@gmail.com','Male',1) INSERT INTO tblStudent VALUES(1002,'Pooja kumari','p@gmail.com','Female',2) INSERT INTO tblStudent VALUES(1003,'Arun kuamr','a1@gmail.com','Male',1) INSERT INTO tblStudent VALUES(1004,'Pragya','p@gmail.com','Female',3) INSERT INTO tblStudent VALUES(1005,'Manorma','m@gmail.com','Female',4) INSERT INTO tblStudent VALUES(1006,'Ojha','o@gmail.com','Male',4) INSERT INTO tblStudent VALUES(1007,'Kavita','k@gmail.com','Female',2) INSERT INTO tblStudent VALUES(1008,'Dr. Prem kumar','pk@gmail.com','Male',4) INSERT INTO tblStudent VALUES(1009,'Arti','a@gmail.com','Female',1) ----------- Insert records into tblBranch-------------- INSERT INTO tblBranch VALUES(1,'IT') INSERT INTO tblBranch VALUES(2,'CSE') INSERT INTO tblBranch VALUES(3,'ECE') INSERT INTO tblBranch VALUES(4,'ME')
tblBranch
Since, we now have the required tables, let's create a view based on these tables. The view should return Employee Id, Name, Email, Gender and BranchName columns. So, the view is obviously based on multiple tables.
Create a view for above requirement:-
Create view vwEmployeeRecord as Select Id, Name,Email, Gender, BranchName from tblStudent join tblBranch on tblStudent.BranchId = tblBranch.BranchId ---------Run the view 'vwEmployeeRecord'--------------- SELECT * FROM vwEmployeeRecord
Now, we try to insert record into view vwEmployeeRecord. It witll be give following errors.
View or function 'vwEmployeeRecord' is not updatable because the modification affects multiple base tables.
So, inserting a row into a view that is based on multipe tables, raises an error by default. Now, let's understand, how INSTEAD OF TRIGGERS can help us in this situation. Since, we are getting an error, when we are trying to insert a row into the view, let's create an INSTEAD OF INSERT trigger on the view vwEmployeeRecord.
Create INSTEAD OF INSERT trigger:
Create trigger tr_vwEmployeeRecords_InsteadOfInsert on vwEmployeeRecord Instead Of Insert as Begin Declare @BranchId int --Check if there is a valid BranchId --for the given BranchName Select @BranchId = BranchId from tblBranch join inserted on inserted.BranchName = tblBranch.BranchName --If BranchId is null throw an error --and stop processing if(@BranchId is null) Begin Raiserror('Invalid Branch Name. Statement terminated', 16, 1) return End --Finally insert into tblEmployee table Insert into tblStudent (Id, Name,Email, Gender, BranchId) Select Id, Name,Email, Gender, @BranchId from inserted End