Instead of Update Trigger in SQL Server

Instead of Update Trigger

To Create INSTEAD OF UPDATE Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

-- We create tblDeparment

create table tblDeparment(
Id int identity(1,1) primary key not null,
DeparmentName varchar(30) not null,
Location Char(8) not null,
DeparmentHead varchar(20) not null
)

--Insert data into tblDeparment

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--Now crate table tblEmployee

create table tblEmployee(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--Insert data into tblEmployee 

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee “ which are inter-connected through Foreign key constraint.

We will apply trigger for these tables.

Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data.

Created All tables for Instead of Update Triggers

Now create the INSTEAD OF UPDATE Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfUpdate
on tblEmployee
INSTEAD OF update
AS
  BEGIN
     Declare @EmpId int,               
             @EmpMessage varchar(200),
             @Name varchar(30), @Gender varchar(8),
             @DepartmentId int;
     select  @EmpId =D.Id 
             from deleted D;
     select  @Name = I.Name, 
             @Gender = I.Gender,
             @DepartmentId= DeparmentId from Inserted I;
             
      
     If(Not exists(Select Id from tblEmployee where Id = @EmpId))   --If Id not Exists in Cloumn than through message. you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           SET @EmpMessage  = 'Employee With Id ' + CAST(@EmpId as varchar(5)) + 'Not Exists in the DataBase'
           Raiserror(@EmpMessage, 16, 1)
           Print 'Employee Not Exists'
           RollBack
       End
       Else
          Begin
            if(UPDATE(Name))
              begin
                 update tblEmployee set Name = @Name where Id= @EmpId
              end
             If(UPDATE(Gender))
               begin
                 update tblEmployee set Gender =@Gender where Id= @EmpId
               end
             If(UPDATE(DeparmentId))
               begin
                 update tblEmployee set DeparmentId =@DepartmentId where Id= @EmpId
               end
             insert into tblAudit values('Employee succefully Update with Id ' + CAST(@EmpId as varchar(5)) + 'and insert into trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
  END

Now try to Update data from tblEmployee, If we try to update id which is not exists than return message Employee with Id not exits

update tblEmployee set Name= 'janee' where id=5

Show Message Employee Not Exit

Now we try to Update id which is already exists return message successfully row effect

update tblEmployee set Name= 'janee' where id=1

Now check data from tables.

Select all tables After, Instead of  Update Trigger fire

Example 2

For more understand about INSTEAD OF UPDATE Trigger we take another trigger example.

First we create another table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpOldName varchar(30) Not Null,
  EmpNewName varchar(30) Not Null,
  EmpOldGender char(8) Not Null,
  EmpNewGender char(8) Not Null,
  EmpOldDeparmentId int Not Null,
  EmpNewDeparmentId int Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create the INSTEAD OF UPDATE Trigger for “tblEmployee”

create trigger trtblEmployeeInsteadOfDeleteByDetail
ON tblEmployee
INSTEAD OF UPDATE
As
 BEGIN
     Declare @EmpId int, 
             @OldEmpName varchar(30), @NewEmpName varchar(30),            
             @OldEmpGender varchar(8), @NewEmpGender varchar (8),
             @OldEmpDepatmentId int, @NewEmpDepatmentId int,
             @EmpMessage varchar(300),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
     select  @EmpId=D.Id,             --Id Not Change
             @OldEmpName = D.Name, 
             @OldEmpGender = D.Gender,
             @OldEmpDepatmentId = D.DeparmentId from deleted D;
     select  @NewEmpName = I.Name, --delete not only keep id column value, keep all column value for which id we want delete. 
             @NewEmpGender= I.Gender, @NewEmpDepatmentId= I.DeparmentId
             from inserted I
      
     If not exists(Select Id from tblEmployee where Id = @EmpId)   --If Id not Exists in Cloumn than through message, you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           SET @EmpMessage  = 'Employee With Id ' + CAST(@EmpId as varchar(5)) + 'Not Exists in the DataBase.'
           Raiserror(@EmpMessage, 16, 1)
           Print 'INSTEAD OF Update Triggers in SQL Server not fire because Employee Not Exists' 
           RollBack
       End
       Else
          Begin
             SET @AuditAction='Update Record '
             If(@OldEmpName <> @NewEmpName)
               begin
                  set @AuditAction = @AuditAction + 'Name from ' + @OldEmpName + 'to ' + @NewEmpName + ', '
                  update tblEmployee set Name= @NewEmpName where id=@EmpId
               end
             If(@OldEmpGender <> @NewEmpGender)
               begin
                 set @AuditAction =@AuditAction + 'Gender from ' + @OldEmpGender + 'to ' + @NewEmpGender + ', '
                 update tblEmployee set Gender= @NewEmpGender where id=@EmpId
               end
             If(@OldEmpDepatmentId <> @NewEmpDepatmentId)
               begin
                 set @AuditAction =@AuditAction + 'DeaparmentId from ' + CAST(@OldEmpDepatmentId as varchar(10)) + 'to ' + CAST(@NewEmpDepatmentId As varchar(10))+ ', '
                 update tblEmployee set DeparmentId= @NewEmpDepatmentId where id=@EmpId
               end
    set @ActionTakenBy = SYSTEM_USER;
 set @ServerName = CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50));          -- select @@SERVERNAME return server name
 set @ServerInstanceName = CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50));    --SELECT @@servicename return current machine name
             
            insert into tblAuditDetailsBy (EmpId, EmpOldName, EmpNewName, EmpOldGender, EmpNewGender,
                                 EmpOldDeparmentId, EmpNewDeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@OldEmpName, @NewEmpName, @OldEmpGender, @NewEmpGender,
                                @OldEmpDepatmentId, @NewEmpDepatmentId, @AuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Delete Data with ID= ' + CAST(@EmpId as varchar(5)) + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF Update Triggers in SQL Server'
          End
 END

Now try to Update data from tblEmployee, If we try to Update id which is not exists than return message Employee with Id not exits

update tblEmployee set Name= 'jhone', Gender='Male' where id=5

But If we try to Update id which is already exists return message successfully row effect

update tblEmployee set Name= 'jhone', Gender='Male' where id=1

Update Tables

Now check data from tables.

previous Related Post

Read More

instead of delete trigger in SQL Server using views

To Create INSTEAD OF DELETE Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We create table tblDeparment

create table tblDeparment(
Id int identity(1,1) primary key not null,
DeparmentName varchar(30) not null,
Location Char(8) not null,
DeparmentHead varchar(20) not null
)

--insert data's into tblDeparment

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--Create table tblEmployee

create table tblEmployee(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--insert data's into tblEmployee 

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee “ which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.
Now create Views.

create View VwTblEmployeeJoinWithtblDepartment
as
select tblEmployee.Id, Name, Gender, DeparmentName
from tblEmployee
join tblDeparment
on tblEmployee.DeparmentId = tblDeparment.Id

Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have tables and views are given below with data.

We have all tables for instead of delete trigger using vies

If we try to delete data from view than not delete show an error multiple base table affects.

Try to delete data from view than show an error

So create the INSTEAD OF DELETE Trigger for “VwTblEmployeeJoinWithtblDepartment”

create trigger trtblEmployeeInsteadOfDeatilByView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF DELETE
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentName varchar(20),
             @EmpMessage varchar(200);
     select  @EmpId = D.Id, @EmpName = D.Name, 
             @EmpGender= D.Gender, @EmpDepatmentName= D.DeparmentName
             from deleted D              --Here inserted table keep data which we want insert through view.
      
          If(Not exists(Select id from tblEmployee where id = @EmpId))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
             Begin
              Raiserror('Employee With Id Not Exists in the DataBase.', 16, 1)
              Print 'INSTEAD OF DELETED Triggers in SQL Server not fire.' 
              RollBack
             End
           Else
             Begin
              delete tblEmployee from tblEmployee --delete command 
              join deleted
              on tblEmployee.Id = deleted.Id                         
              insert into tblAudit values('Employee succefully deleted with Id ' + CAST(@EmpId as varchar(5)) + 'in trigger in date' + CAST(Getdate() AS nvarchar(30)))
             End
      
 END

Now delete data from view.

delete from VwTblEmployeeJoinWithtblDepartment where Id=2

Now check tables and view.

select All tables and view after fire trigger

Example 2
For more understand about INSTEAD OF INSERT Trigger by view when multiple base table update so we take another trigger example.
First we create another table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  EmpGender char(8) Not Null,
  EmpDeparmentName varchar(50) Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create the INSTEAD OF DELETE Trigger for “VwTblEmployeeJoinWithtblDepartment”

create trigger trtblEmployeeInsteadOfDeleteByDetailView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF DELETE
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @DeparmentName varchar(50),
             @EmpMessage varchar(300),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
             
     select  @EmpId=D.Id, @EmpName = D.Name, --delete not only keep id column value, keep all column value for which id we want delete. 
             @EmpGender= D.Gender, @DeparmentName= D.DeparmentName
             from deleted D
      
     If not exists(Select Id from tblEmployee where Id = @EmpId)   --If Id not Exists in Cloumn than through message, you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           Raiserror('Id does not exists', 16, 1)
           Print 'INSTEAD OF DELETED Triggers in SQL Server not fire because Id does not exists'
           RollBack
       End
       Else
          Begin
              delete tblEmployee from tblEmployee --delete command 
              join deleted
              on tblEmployee.Id = deleted.Id                                      
             
             SET @AuditAction='Delete Record'
             SET @ActionTakenBy= SYSTEM_USER
             SET @ServerName = CAST(SERVERPROPERTY('ServerName') as varchar(100))
             SET @ServerInstanceName = CAST(SERVERPROPERTY('MachineName') as varchar)
             insert into tblAuditDetailsBy(EmpId, EmpName, EmpGender, EmpDeparmentName, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @DeparmentName, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Delete Data with ID= ' + CAST(@EmpId as varchar(5)) + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF Delete Triggers in SQL Server'
          End
 END

Now delete data from view VwTblEmployeeJoinWithtblDepartment.

delete from VwTblEmployeeJoinWithtblDepartment where Id = 2

Try to delete data from view than fire Instead of delete trigger

Now check tables and view.

previous Related Post

Read More

Instead of delete trigger in sql server

INSTEAD OF INSERT Trigger

To Create INSTEAD OF DELETE Triggers first of all we need to create a table (but we create two table) for which we apply trigger and we need another table that hold all activity in main table.

--We create tblDeparment table

create table tblDeparment(
Id int identity(1,1) primary key not null,
DeparmentName varchar(30) not null,
Location Char(8) not null,
DeparmentHead varchar(20) not null
)

--Now insert data into tblDeparment 

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We create tblEmployee table

create table tblEmployee(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--Now insert data into tblEmployee 

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee“ which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.

Example 1 Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data.

All table are given

Now create the INSTEAD OF DELETE Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfDelete
on tblEmployee
INSTEAD OF DELETE
AS
  BEGIN
     Declare @EmpId int,               
             @EmpMessage varchar(200);
     select  @EmpId =D.Id 
             from deleted D
      
     If(Not exists(Select Id from tblEmployee where Id = @EmpId))   --If Id not Exists in Cloumn than through message. you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           SET @EmpMessage  = 'Employee With Id ' + CAST(@EmpId as varchar(5)) + 'Not Exists in the DataBase'
           Raiserror(@EmpMessage, 16, 1)
           Print 'Employee Not Exists'
           RollBack
       End
       Else
          Begin
             delete from tblEmployee where Id= @EmpId     --delete command      
             insert into tblAudit values('Employee succefully Delete with Id ' + CAST(@EmpId as varchar(5)) + 'and insert into trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
  END

Now try to delete data from tblEmployee.
If we try to delete id which is not exists than return message Employee with Id not exits delete from tblEmployee where Id=5.

Try to delete from table

Now we try to delete id which is already exists return message succefully row effect delete from tblEmployee where Id=4.

Try to delete from table

Now check data from tables.

Selete table after fire instead of delete trigger

Example 2

For more understand about INSTEAD OF DELETE Trigger we take another trigger example.

First we create another table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  EmpGender char(8) Not Null,
  EmpDeparmentId int Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create the INSTEAD OF DELETE Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfDeleteByDetail
ON tblEmployee
INSTEAD OF DELETE
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentId int,
             @EmpMessage varchar(300),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
             
     select  @EmpId=D.Id, @EmpName = D.Name, --delete not only keep id column value, keep all column value for which id we want delete. 
             @EmpGender= D.Gender, @EmpDepatmentId= D.DeparmentId
             from deleted D
      
     If not exists (Select Id from tblEmployee where Id = @EmpId)   --If Id not Exists in Cloumn than through message, you can check other condition like departmentId =1 or Gender=Male, than you can print message you can not delete
       Begin
           SET @EmpMessage  = 'Employee With Id ' + CAST(@EmpId as varchar(5)) + 'Not Exists in the DataBase.'
           Raiserror(@EmpMessage, 16, 1)
           Print 'INSTEAD OF DELETED Triggers in SQL Server not fire because ' + @EmpMessage
           RollBack
       End
       Else
          Begin
             delete from tblEmployee where Id= @EmpId --delete command                             
             
             SET @AuditAction='Delete Record'
             SET @ActionTakenBy= SYSTEM_USER
             SET @ServerName = CAST(SERVERPROPERTY('ServerName') as varchar(100))
             SET @ServerInstanceName = CAST(SERVERPROPERTY('MachineName') as varchar)
             insert into tblAuditDetailsBy(EmpId, EmpName, EmpGender, EmpDeparmentId, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @EmpDepatmentId, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Delete Data with ID= ' + CAST(@EmpId as varchar(5)) + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF Delete Triggers in SQL Server'
          End
 END

Now try to delete data from tblEmployee.
If we try to delete id which is not exists than return message Employee with Id not exits delete from tblEmployee where Id=5.
And if try to delete id which is already exists return message successfully row effect

delete from tblEmployee where Id=2

Delete data from table than Instead of delete trigger fire

 Now check data from tables.

previous Related Post

Read More

instead of insert trigger in sql server on views

Trigger on view using INSTEAD OF INSERT

To Create INSTEAD OF INSERT triggers in sql server first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We Create table tblDeparment table

create table tblDeparment(
Id int identity(1,1) primary key not null,
DeparmentName varchar(30) not null,
Location Char(8) not null,
DeparmentHead varchar(20) not null
)

--insert data's into tblDeparment table

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--Create another table tblEmployee table

create table tblEmployee(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)able

--Insert data into tblEmployee table

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee“ which are inter-connected through Foreign key constraint. We will apply trigger for these tables.

Now create Views.

create View VwTblEmployeeJoinWithtblDepartment
as
select tblEmployee.Id, Name, Gender, DeparmentName
from tblEmployee
join tblDeparment
on tblEmployee.DeparmentId = tblDeparment.Id

Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have tables and views are given below with data.

All table for INSTEAD OF INSERT using Views

Now Insert data into view "VwTblEmployeeJoinWithtblDepartment", but not updatable because affect multiple base table, so use INSTEAD OF INSERT Trigger.

insert into VwTblEmployeeJoinWithtblDepartment values('Jhon', 'Male', 'IT') 

insert row into views

Now create sql server audit table trigger on view by INSTEAD OF INSERT Trigger on views for “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsertByView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF INSERT
As
 BEGIN
     declare @DepartId int
     select @DepartId =tblDeparment.Id from tblDeparment
     join inserted
     on inserted.DeparmentName = tblDeparment.DeparmentName    --DeparmentName of inserted(here from view) = DeparmentName of tblDeparment
     
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentName varchar(20),
             @EmpMessage varchar(200);
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentName= I.DeparmentName
             from inserted I              --Here inserted table keep data which we want insert through view.
      
       if(@DepartId is Null)
         Begin
           Raiserror('Invalid Department Id', 16, 1);
           Print 'Invalid Department Id' 
           RollBack
         End   
      Else
         Begin
          If(exists(Select Name from tblEmployee where Name = @EmpName))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
             Begin
              SET @EmpMessage  = 'Employee With Name ' + @EmpName + 'alredy Register with this Name'
              Raiserror(@EmpMessage, 16, 1)  --16 seberaty level(there are several seberaty level) means user can't correct or resubmit the query
              RollBack
             End
           Else
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@DepartId)
                               
             /*insert into VwTblEmployeeJoinWithtblDepartment values(8,'Jhon3', 'Male3', 'IT')      
             you cant update using view return error is not updatable because the modification affects multiple base tables*/
             
            /*If we commented Above line and than try to insert data into tblEmployee message return
             row affected but basically does not insert data in tblEmployee because of INSTEAD OF TRIGGER 
             and in tblAudit table NULL value will be insert because there is no last generated SCOPE_IDENTITY().
             So if you want to insert than must write insert querry here.*/                               
             select @EmpId=SCOPE_IDENTITY() from tblEmployee;
             insert into tblAudit values('Employee succefully insert with Id ' + CAST(@EmpId as varchar(5)) + 'in trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
       End
 END

Now Insert data into view "VwTblEmployeeJoinWithtblDepartment" (which is combination of two table “tblemployee” and “tbldepartmet” which is enter connected with foreign key) with Garbage value for “DepartmentName” which is not in “tblDepartment” table so return a message invalid.

insert into VwTblEmployeeJoinWithtblDepartment values(1,'Jhonny', 'Male', 'GarbageValue') --id=1 insert but it is false because view require id and table “tblEployee” has id is identity. so table automatically set increment id.

inter row into views with garbage value

Now insert with correct data into “VwTblEmployeeJoinWithtblDepartment”, which is available in “tblDepartment” table.

insert into VwTblEmployeeJoinWithtblDepartment values(1,'Jhonny', 'Male', 'HR') 

audit data of Instead of Insert using views

Example 2
For more understand about INSTEAD OF INSERT trigger on view, when multiple base table update so we take another trigger example.

First we create another table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  EmpGender char(8) Not Null,
  EmpDeparmentId int Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create the INSTEAD OF INSERT Trigger on views for “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsertByDetailView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF INSERT
As
 BEGIN
     Declare @DepartId int
     Select @DepartId= tblDeparment.Id from tblDeparment
     join inserted
     on inserted.DeparmentName = tblDeparment.DeparmentName    
     
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentName varchar(20),
             @EmpMessage varchar(200),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
            
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentName= I.DeparmentName
            from inserted I      --Here inserted table keep data which we want insert through view.
      if(@DepartId is Null)
         Begin
           Raiserror('Invalid Department Id', 16, 1);
           Print 'Invalid Department Id' 
           RollBack
         End      
      Else
        Begin
          If(exists(Select Name from tblEmployee where Name = @EmpName))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
             Begin
              SET @EmpMessage  = 'Employee With Name ' + @EmpName + 'alredy Register with this Name'
              Raiserror(@EmpMessage, 16, 1);
              PRINT 'INSTEAD OF INSERT Triggers in SQL Server not fire because ' + @EmpMessage
              RollBack
             End
          Else
            Begin
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@DepartId)
            /*If we commented Above line and than try to insert data into tblEmployee message return
             row affected but basically does not insert data in tblEmployee because of INSTEAD OF TRIGGER 
             and in tblAudit table NULL value will be insert because there is no last generated SCOPE_IDENTITY().
             So if you want to insert than must write iinsert querry here.*/                               
             select @EmpId=SCOPE_IDENTITY() from tblEmployee;
             SET @AuditAction='Insert Record'
             SET @ActionTakenBy= SYSTEM_USER
             SET @ServerName = CAST(SERVERPROPERTY('ServerName') as varchar(100))
             SET @ServerInstanceName = CAST(SERVERPROPERTY('MachineName') as varchar)
             insert into tblAuditDetailsBy(EmpId, EmpName, EmpGender, EmpDeparmentId, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @DepartId, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Insert Data with ID=' + @EmpId + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF INSERT Triggers in SQL Server'
            End
         End
 END

Now Insert data into view “VwTblEmployeeJoinWithtblDepartment” with Garbage value for “DepartmentName” which is not in “tblDepartment” table so return an error message.

insert into “VwTblEmployeeJoinWithtblDepartment” values(1,'Jhonnifer', 'Female', 'GarbageValue') --id=1 insert but it is false because view require id and table “tblEployee” has id is identity. so table automatically set increment id.
--If enter already Exists name than through an error message also.

insert data into views with garbage value

Now insert with correct data, which is available in “tblDepartment” table.

insert into VwTblEmployeeJoinWithtblDepartment values(1,'Jhonny', 'Male', 'HR') 

Now check tables and views.

Audit Details when insert data in view using Instead of insert trigger

previous Related Post Next Related Post

Read More

Sql Server Trigger - Instead of insert trigger

To Create INSTEAD OF INSERT Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We cretate tblDeparment table
create table tblDeparment
(
 Id int identity(1,1) primary key not null,
 DeparmentName varchar(30) not null,
 Location Char(8) not null,
 DeparmentHead varchar(20) not null
)

--Insert data into tblDeparment tables

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We cretate tblEmployee table

create table tblEmployee(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--Insert data into tblEmployee tables

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee “ which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.

Example 1

Now create audit table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data's.

we have tables for INSTEAD OF INSERT Trigger

Now create sql server audit table trigger for INSTEAD OF INSERT Trigger on “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsert
ON tblEmployee
INSTEAD OF INSERT
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentId int,
             @EmpMessage varchar(200);
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentId= I.DeparmentId
            from inserted I
      
     If(exists(Select Name from tblEmployee where Name = @EmpName))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
       Begin
           SET @EmpMessage  = 'Employee With Name ' + @EmpName + 'alredy Register with this Name'
           Raiserror(@EmpMessage, 16, 1)
           RollBack
       End
       Else
          Begin
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@EmpDepatmentId)
            /*If we commented Above line and than try to insert data into tblEmployee message return
             row affected but basically does not insert data in tblEmployee because of INSTEAD OF TRIGGER 
             and in tblAudit table NULL value will be insert because there is no last generated SCOPE_IDENTITY().
             So if you want to insert than must write iinsert querry here.*/                               
             select @EmpId=SCOPE_IDENTITY() from tblEmployee;
             insert into tblAudit values('Employee succefully insert with Id ' + CAST(@EmpId as varchar(5)) + 'in trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
 END

Now try to insert data into "tblEmployee".
If we try to insert Name which is already exists than return message Employee with name already exits

insert into tblEmployee values('Jane', 'Male', 4) 

INSTEAD OF INSERT Trigger fire if already exists row, than return message

Now we try to insert Name which is not already exists return message succefully insert.

insert into tblEmployee values('Jhon', 'Male', 4)

Now check data from tables.

Row insert into Audit table when fire INSTEAD OF INSERT Trigger

Example 1

For more understand about INSTEAD OF INSERT Trigger we take another ms sql trigger example. First we create another audit table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  EmpGender char(8) Not Null,
  EmpDeparmentId int Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create the INSTEAD OF INSERT Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsertByDetail
ON tblEmployee
INSTEAD OF INSERT
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentId int,
             @EmpMessage varchar(200),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
             
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentId= I.DeparmentId
            from inserted I
      
     If(exists(Select Name from tblEmployee where Name = @EmpName))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
       Begin
           SET @EmpMessage  = 'Employee With Name ' + @EmpName + 'alredy Register with this Name'
           Raiserror(@EmpMessage, 16, 1);
           PRINT 'INSTEAD OF INSERT Triggers in SQL Server not fire because ' + @EmpMessage
           RollBack
       End
       Else
          Begin
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@EmpDepatmentId)
            /*If we commented Above line and than try to insert data into tblEmployee message return
             row affected but basically does not insert data in tblEmployee because of INSTEAD OF TRIGGER 
             and in tblAudit table NULL value will be insert because there is no last generated SCOPE_IDENTITY().
             So if you want to insert than must write iinsert querry here.*/                               
             select @EmpId=SCOPE_IDENTITY() from tblEmployee;
             SET @AuditAction='Insert Record'
             SET @ActionTakenBy= SYSTEM_USER
             SET @ServerName = CAST(SERVERPROPERTY('ServerName') as varchar(100))
             SET @ServerInstanceName = CAST(SERVERPROPERTY('MachineName') as varchar)
             insert into tblAuditDetailsBy(EmpId, EmpName, EmpGender, EmpDeparmentId, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @EmpDepatmentId, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Insert Data with ID=' + CAST(@EmpId as varchar(5)) + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF INSERT Triggers in SQL Server'
          End
 END

Now try to insert data into "tblEmployee".
Now we try to insert Name which is not already exists return message successfully insert.

insert into tblEmployee values('Jhon', 'Male', 4) 

INSTEAD OF INSERT Trigger fire when insert row

Now check data from database triggers in sql tables.

Insert row into audit table when fire INSTEAD OF INSERT Trigger

Read More

AFTER UPDATE Trigger in sql server

AFTER UPDATE Trigger

To Create AFTER Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We create tblDeparment table below

create table tblDeparment
(
Id int identity(1,1) primary key not null,
DeparmentName varchar(30) not null,
Location Char(8) not null,
DeparmentHead varchar(20) not null
)

--We insert following data's into tblDeparment below

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We create tblEmployee table below
create table tblEmployee
(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)    --foreign key
)

--We insert following data's into tblEmployee below

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee “ which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.

Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have tables are given below with datas.

Available Tables are selected

Now create the AFTER UPDATE Trigger for “tblEmployee”.

Create trigger trtblEmployeeForUpdate
on tblEmployee                                   
For UPDATE                                        --for Update trigger                                   
As
Begin
    Declare @Id int                                          --Id not change because of primary key
    Select @Id = Id from inserted                            --inserted table use by SQL Server to keep copy of Row which just insert into actual table, the structure of the inserted table is identical to the structure of the actual table
    insert into tblAudit values('An Exiting Employee With Id = ' + cast(@Id as varchar(10)) + 'is Update at' + cast(GETDATE() as nvarchar(20))) --Foreign Key

--CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
End

Now try to Update data from “tblEmployee” than "trtblEmployeeForDelete" trigger automatically fire and data insert into "tblAudit" and also show in message for two row affected.

delete from tblEmployee where Id=4

After Update trigger update the table

 Now check data from tables.

Select table After update trigger

Example 2

For more understand about AFTER DELETE Trigger we write above Query by different way when more than one update Query execute.

create trigger trtblEmployeeForUpdateByComprision
On tblEmployee
For Update                          --for Update trigger
As
Begin
    Declare @Id int                 --Declare variable Id Not Change because it is Primary key
    Declare @Old_Name nvarchar(30)
    Declare @Old_Gender nvarchar(8)
    declare @Old_DepartmentId int
    
    Declare @New_Name nvarchar(30)
    Declare @New_Gender nvarchar(8)
    declare @New_DepartmentId int
    
    declare @AuditDetails nvarchar(2000)
    
    Select * into #tblTemptblEmployee from inserted               --select data from inserted table (New data which want to update) and insert into temporary table "#tblTemptblEmployee"
    
    while(Exists (Select Id from #tblTemptblEmployee))            --check condition, Id is available or not for which we want to update
    Begin
        Set @AuditDetails = ''
        
        Select Top 1 @Id = Id,                          -- if there are many row in temporary table for updatation, than select top 1 row by id and put into @Id variable
               @New_Name =Name, @New_Gender = Gender,   --since inserted table contain new data, so take data from trigger inserted table(which are transfer into temporary table) and put int @New_Name variable, similarly all
               @New_DepartmentId = DeparmentId
        From #tblTemptblEmployee                        -- select from from temporary table new value and assign to variables
        
        Select @Old_Name = Name, @Old_Gender = Gender,
               @Old_DepartmentId = DeparmentId
        From deleted Where Id= @Id                       --select from deleted table
        
        Set @AuditDetails= 'Employee With Id = ' + Cast(@Id as nvarchar(5)) + 'Changed '        --CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
        
        If(@Old_Name <> @New_Name)                                                              --check old name not equal to new name
        Set @AuditDetails= @AuditDetails + 'Name From' + @Old_Name + 'To ' + @New_Name          
        
        If(@Old_Gender <> @New_Gender)
        set @AuditDetails = @AuditDetails + ' New Gender ' + @Old_Gender + 'To ' + @New_Gender
        
        If(@Old_DepartmentId <> @New_DepartmentId)
        set @AuditDetails = @AuditDetails + ' New DeparmentId ' + Cast(@Old_DepartmentId as nvarchar(5)) + 'To ' + Cast(@New_DepartmentId as nvarchar(5)) 
        
        insert Into tblAudit values(@AuditDetails)
        delete from #tblTemptblEmployee where Id = @Id         --delete the data from temporary table otherwise it become infinite loop
    End
End 

Now try to Update data from “tblEmployee” than "trtblEmployeeForUpdateByComprision" trigger automatically fire and data insert into "tblAudit" and also show in message for no of row affected.

Update tblEmployee set Name='Rosery' where id=2

After updating check data from tables

Select tables after update the trigger

Now Update two row simultaneously.

update tblEmployee set Name='Rose Dosan1', Gender='Male' where id =2
update tblEmployee set Name='Rose Dosan4', Gender='Female' where id =4

After updating check data again from tables

After Update trigger in Sql Server

Now Update By IN

update tblEmployee set DeparmentId=4 where id IN(1,3)

Now check data from tables.

Select Table After update

Example 3

For more understand about AFTER Update Trigger we take another trigger example.
First we create another table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpOldName varchar(30) Not Null,
  EmpNewName varchar(30) Not Null,
  EmpOldGender char(8) Not Null,
  EmpNewGender char(8) Not Null,
  EmpOldDeparmentId int Not Null,
  EmpNewDeparmentId int Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create AFTER UPDATE Trigger.

CREATE TRIGGER trtblEmployeeForAfterUpdate
On tblEmployee
AFTER UPDATE                               -- We can use AFTER instead of FOR, in above Query we use FOR
AS
 Begin
 declare @EmpId int;            --Declare variable Id Not Change because it is Primary key
 
 declare @EmpOldName varchar(30);
 declare @EmpOldGender varchar(8);
 declare @EmpOldDepartmentId int;
 
 declare @EmpNewName varchar(30);
 declare @EmpNewGender varchar(8);
 declare @EmpNewDepartmentId int;
 
 declare @EmpAuditAction varchar(1000);
 declare @ActionTakenBy varchar(100);
    declare @ServerName varchar(100);
    declare @ServerInstanceName varchar(100);
    
    set @EmpAuditAction='Update Record ';
 select @EmpId=I.Id from inserted I;         --select value from inserted table(update ) and assign to @EmpId
  
 select @EmpNewName=I.Name,               --select value from inserted table(which we want to update) and assign to @EmpName
        @EmpNewGender=I.Gender,
        @EmpNewDepartmentId=I.DeparmentId from inserted I; 
 
 select @EmpOldName=D.Name,                    --select value from deleted table(old value) and assign to @EmpId
        @EmpOldGender=D.Gender,
        @EmpOldDepartmentId=D.DeparmentId from deleted D;
 
 If(@EmpNewName <> @EmpOldName)    -- We can use also If Update(Name), Name is tblEmployee ColumnName
 Begin  
   set @EmpAuditAction = @EmpAuditAction + ' Name from ' + @EmpOldName + ' to ' + @EmpNewName + ', ' 
 End
 If(@EmpNewGender <> @EmpOldGender)   
 set @EmpAuditAction = @EmpAuditAction + ' Gender from ' + @EmpOldGender + 'to ' + @EmpNewGender + ', '
 If(@EmpNewDepartmentId <> @EmpOldDepartmentId)   
 set @EmpAuditAction = @EmpAuditAction + ' Department Id from ' + CAST(@EmpOldDepartmentId as varchar(5)) + ' to ' + CAST(@EmpNewDepartmentId as varchar(5))
 
 
 set @ActionTakenBy = SYSTEM_USER;
 set @ServerName = CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50));          -- select @@SERVERNAME return server name
 set @ServerInstanceName = CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50));    --SELECT @@servicename return current machine name
 
 insert into tblAuditDetailsBy (EmpId, EmpOldName, EmpNewName, EmpOldGender, EmpNewGender,
                                 EmpOldDeparmentId, EmpNewDeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@EmpOldName, @EmpNewName, @EmpOldGender, @EmpNewGender,
                                @EmpOldDepartmentId, @EmpNewDepartmentId, @EmpAuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)

 PRINT 'Successfully Fired the AFTER Update Triggers in SQL Server'
End

Now try to update data from "tblEmployee" than "trtblEmployeeForAfterUpdate" trigger automatically fire and data insert into "tblAuditDetailsBy" and also show in message row affected.

update tblEmployee set Name='John', Gender='Male' where id =1
update tblEmployee set Name='Leeky', Gender='Female', DeparmentId=2 where id =2

Update after update trigger

Now Check data from tables.

Select table aftre update trigger fire

Read More

AFTER DELETE Trigger in SQL Server with examples

AFTER DELETE Trigger

To Create AFTER Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We Create below tbldepartment Table

create table tblDeparment
(
 Id int identity(1,1) primary key not null,
 DeparmentName varchar(30) not null,
 Location Char(8) not null,
 DeparmentHead varchar(20) not null
)

--We Insert followings datas in tblDepartment

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We create another table tblEmployee

create table tblEmployee
(
 Id int identity(1,1) primary key not null,
 Name varchar(30) not null,
 Gender Char(8) not null,
 DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--insert data into table tblEmployee
insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee“  which are inter-connected through Foreign key constraint.
Now we will apply trigger for these tables.

Example 1
First of all we need to create table that hold activity in main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)     --keep all activity in his cloumn
) 

Now we have tables are given below with data.

Now create the AFTER DELETE Trigger for “tblEmployee”.

Create trigger trtblEmployeeForDelete
on tblEmployee
For Delete
As
Begin
    Declare @Id int
    Select @Id = Id from deleted         --Deleted table use by SQL Server to keep copy of Row which just delete from actual table, the structure of the deleted table is identical to the structure of the actual table
    insert into tblAudit values('An Exiting Employee With Id = ' + cast(@Id as varchar(10)) + 'is Deleted at' + cast(GETDATE() as nvarchar(20)))

--CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
End

Now try to delete data from “tblEmployee” than "trtblEmployeeForDelete" trigger automatically fire and data insert into "tblAudit" and also show in message for affected rows.

delete from tblEmployee where Id=4

Now check data from tables.

Example 2
For more understand about AFTER DELETE Trigger we take another trigger example.
First we create another table that hold all activity of main table done by user.

Create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  Gender char(8) Not Null,
  DeparmentId int Not Null,
  AuditAction varchar(30) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create AFTER DELETE Trigger.

CREATE TRIGGER trtblEmployeeForAfterDelete
On tblEmployee
AFTER DELETE                 -- We can use AFTER instead of FOR, in above Query we use FOR
AS
 Begin
 declare @EmpId int;        --Declare variables
 declare @EmpName varchar(30);
 declare @EmpGender varchar(8);
 declare @EmpDepartmentId int;
 declare @EmpAuditAction varchar(100);
 declare @ActionTakenBy varchar(100)
 declare @ServerName varchar(100);
 declare @ServerInstanceName varchar(100);
    
 select @EmpId=D.Id from deleted D;     --Select Id from deleted and assign to @EmpId. (D is alias name for deleted table)
 select @EmpName=D.Name from deleted D; 
 select @EmpGender=D.Gender from deleted D;
 select @EmpDepartmentId=D.DeparmentId from deleted D; 
 set @EmpAuditAction='Deleted Record ';
 set @ActionTakenBy = SYSTEM_USER;
 set @ServerName = CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50));          -- select @@SERVERNAME, return server name
 set @ServerInstanceName = CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50));    --SELECT @@servicename, return current machine name
 
 insert into tblAuditDetailsBy (EmpId, EmpName, Gender, DeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@EmpName,@EmpGender,@EmpDepartmentId,@EmpAuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)

 PRINT 'Successfully Fired the AFTER DELETE Triggers in SQL Server'
End

Now try to delete data from "tblEmployee" than "trtblEmployeeForAfterInsert" trigger automatically fire and data insert into "tblAuditDetailsBy" and also show in message for two row affected.

delete from tblEmployee where Id=2

Now Check data's from tables

Read More

AFTER INSERT Trigger in SQL Server with examples

AFTER INSERT Trigger

AFTER INSERT Trigger fire after the triggering action Insert but before competition to the databases. AFTER Insert Trigger has the ability that it can rollback modification action or statements.

To Create AFTER INSERT Trigger first of all we need to create a table (but we create two tables) for which we apply Trigger and need another table that hold all activity in main table.

--We create below tblDeparment table

create table tblDeparment
(
 Id int identity(1,1) primary key not null,
 DeparmentName varchar(30) not null,
 Location Char(8) not null,
 DeparmentHead varchar(20) not null
)

--We insert following data into tblDeparment table below

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We create another table tblEmployee 

create table tblEmployee
(
 Id int identity(1,1) primary key not null,
 Name varchar(30) not null,
 Gender Char(8) not null,
 DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)   --foreign key 
)

--We insert following data into tblEmployee table below

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table "tblDeparment" and "tblEmployee" which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.

Inserted and deleted

Before understand trigger we need to understand "inserted" and "deleted" tables which are the special type of the tables use by the trigger and these tables only available in the context of the trigger.

Whenever insert or delete data into or from the table SQL Server manage these tables which retain copy of data which you insert or delete into or from the table.

So when insert or delete data into or from any table SQL Server behind the seen create a table called "inserted" and "deleted" in memory and copy of the row mainted in the inserted and deleted table, and this inserted table can be access inside the context of the trigger.

So structure of "inserted" and "deleted" table identical to the structure or the main table.
If you try to access outside the trigger it will show an error.

--this Trigger only for understand inserted and deleted tables, how usefull for trigger
Create trigger trtblEmployee_ForUnderstand_Inserted_DeletedTable    --"trtblEmployee_ForUnderstand_Inserted_DeletedTable" is a trigger name, tr is use before trigger name for name convention
on tblEmployee                    --create trigger on "tblEmployee"
For update                        --create trigger for update activity
as 
Begin
Select * from inserted            --retain copy of data which you insert into the "tblEmployee".
select * from deleted             --retain copy of data which you delete from the "tblEmployee".          
End

Now create table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data.

Now create the AFTER INSERT Trigger for "tblEmployee".

Create trigger trtblEmployeeForInsert
On tblEmployee
For Insert
As 
Begin
Declare @Id int                         --Declare variable
Select @Id=Id from inserted             --select Id from Inserted table which copy of actual insert data of actaual table and pass to @Id
Insert into tblAudit values('New Employee with Id=' + CAST(@Id as nvarchar(10))+ 'is added at='+ CAST(GETDATE() as nvarchar(20)) )   --insert into tblAudit 

--CAST is used to convert int type Id to nvarchar that concatenate with "New Employee with Id"
End

Now insert data into “tblEmployee” than "trtblEmployeeForInsert" trigger automatically fire and data insert into "tblAudit" and also show in message for two row affected.

insert into tblEmployee values('Collie', 'Male', 2)

Now Check data from tables.

Above Query save data in "trtblEmployeeForInsert" trigger with id and date.


EXAMPLE 2
For more understand about AFTER INSERT Trigger we create take another trigger example.
First we create another table that hold all activity of main table done by user

Create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  Gender char(8) Not Null,
  DeparmentId int Not Null,
  AuditAction varchar(30) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create AFTER INSERT trigger

CREATE TRIGGER trtblEmployeeForAfterInsert
On tblEmployee
AFTER INSERT                 -- We can use AFTER instead of FOR, in above Query we use FOR
AS
 Begin
 declare @EmpId int;
 declare @EmpName varchar(30);
 declare @EmpGender varchar(8);
 declare @EmpDepartmentId int;
 declare @EmpAuditAction varchar(100);
 declare @ActionTakenBy varchar(100);
        declare @ServerName varchar(100);
        declare @ServerInstanceName varchar(100);
    
 select @EmpId=I.Id from inserted I; 
 select @EmpName=I.Name from inserted I; 
 select @EmpGender=I.Gender from inserted I;
 select @EmpDepartmentId=I.DeparmentId from inserted I; 
 set @EmpAuditAction='Inserted Record ';
 set @ActionTakenBy = SYSTEM_USER;
 set @ServerName = CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50));          -- select @@SERVERNAME return server name
 set @ServerInstanceName = CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50));    --SELECT @@servicename return current machine name
 
 insert into tblAuditDetailsBy (EmpId, EmpName, Gender, DeparmentId, AuditAction,
                                ActionTakenBy, AuditTime,ServerName,ServerInstanceName)
                         values(@EmpId,@EmpName,@EmpGender,@EmpDepartmentId,@EmpAuditAction,
                                @ActionTakenBy,GETDATE(), @ServerName, @ServerInstanceName)

 PRINT 'Successfully Fired the AFTER INSERT Triggers in SQL Server'
End

Now insert data into “tblEmployee” than "trtblEmployeeForAfterInsert" trigger automatically fire and data insert into "tblAuditDetailsBy" and also show in message for two row affected.

insert into tblEmployee values('Janiffer', 'Female', 3)

Now check data from tables

Read More