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

  1. AFTER/FOR Trigger
  2. 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 .

DML Trigger

DML Trigger

For Instead of trigger ,as name specified instead of actual logic trigger logic will be executed.

AFTER/FOR Trigger:

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

)


Trigger Creation

CREATE TRIGGER [dbo].[employee_creation]

ON [dbo].[Employee]

FOR INSERT

AS

BEGIN

DECLARE @ID INT

SELECT @ID = EmployeeID FROM inserted

INSERT INTO Employee_Audit VALUES (@ID,'INSERTED')

END

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.

Trigger creation:

CREATE TRIGGER [dbo].[employee_Verification]

ON [dbo].[Employee]

instead of INSERT

AS

BEGIN

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)

END

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.