As we Discussed previously These kind of triggers will be executed when an INSERT,UPDATE,DELETE action takes place on a database table or view.
DML Triggers can be classified into 2 Types
- AFTER/FOR Trigger
- Instead OF Trigger
In the scope of the trigger there are 2 temporary tables are available, they are INSERTED and DELETED which stores the record on which main insert,update or delete operation is happening .
For Instead of trigger ,as name specified instead of actual logic trigger logic will be executed.
Let's look into a example of this kind. This example will show how this trigger is used for tracking purpose of tables. We will create two tables employee and employee_audit and employee_audit table will store what records are inserted into employee table.
Let us create two tables i.e. [dbo].[Employee] and [dbo].[Employee_Audit]
[dbo].[Employee] Creation Syntax
CREATE TABLE [dbo].[Employee]
[EmployeeID] [int] NOT NULL PRIMARY KEY IDENTITY(1,1) ,
[Employee_Name] [nvarchar](50) NULL,
[Employee_Department] [nvarchar](50) NULL
[dbo].[Employee_Audit] Creation Syntax:
CREATE TABLE [dbo].[Employee_Audit]
[EmployeeAuditID] [int] IDENTITY(1,1) PRIMARY KEY,
[EmployeeID] [nvarchar](50) NULL,
[Action] [nvarchar](50) NULL
CREATE TRIGGER [dbo].[employee_creation]
DECLARE @ID INT
SELECT @ID = EmployeeID FROM inserted
INSERT INTO Employee_Audit VALUES (@ID,'INSERTED')
If we try to insert a record to Employee table than new record will be insert into employee_audit table
Syntax: INSERT INTO EMPLOYEE VALUES ('Pallavi','IT')
Instead OF Trigger
In this type of trigger instead of actual statement i.e.INSERT/UPDATE/DELETE statement the statement in the trigger will be executed
Let us look into an example for this kind of trigger.In this example we will only allow an employee who is new i.e., if already that particular employee name is present then we wont allow that record.
CREATE TRIGGER [dbo].[employee_Verification]
instead of INSERT
DECLARE @employee_name nvarchar(50), @employee_department nvarchar(50);
SELECT @employee_name = Employee_Name , @employee_department= Employee_Department FROM inserted;
IF NOT EXISTS(select Employee_Name from Employee WHERE Employee_Name = @employee_name)
INSERT INTO Employee VALUES (@employee_name,@employee_department)
Now we will try insert two record one with new employee name and another with existing employee name. Note that for the previous example we already inserted record for 'Pallavi'. so , now if insert 'pallavi' as employee name that record should not be inserted.
We can see 'Pallavi' is not created again.