Post
Ask Question

Using Trigger In SQL Server To Find Out Date, Time, And Host Name of Record Insertion

Introduction

Triggers are database operations, which are automatically fired when an action such as Insert/Update/Delete is performed on a table or a view in a database.

Triggers are associated with the table or view directly i.e. each table has its own triggers.

Types of triggers

There are two types of Triggers: After and Instead of triggers.

After triggers

The triggers are executed after an action such as an an Insert, Update or Delete is performed.

Instead of Triggers

The triggers are executed instead of any of Insert, Update or Delete operations. For example,

If you mention an Instead of trigger for Delete/remove operation, then whenever a Delete is performed; the trigger will be executed first and if the trigger deletes the record, then only the record will be deleted.
 
Steps

Create one table named tblStaffAudit.
  1. CREATE TABLE tblStaffAudit  
  2. (  
  3.  Id int identity(1,1) primary key,  
  4.  AuditData nvarchar(1000)  
  5. ) 
Create another table named tblStaff.
  1. CREATE TABLE tblStaff  
  2. (  
  3.  Id int Primary Key,  
  4.  Name nvarchar(30),  
  5.  Salary int,  
  6.  Gender nvarchar(10),  
  7.  DepartmentId int  
  8. ) 
Now, insert some dummy records before creating insert trigger.
  1. Insert into tblStaff values (1,'SatyaPrakash1', 5000, 'Male', 3)  
  2. Insert into tblStaff values (2,'SatyaPrakash2', 3400, 'Male', 2)  
  3. Insert into tblStaff values (3,'SatyaPrakash3', 6000, 'Male', 1) 
Now, create an insert trigger.
  1. Create TRIGGER tr_tblStaff_ForInsert  
  2. ON tblStaff  
  3. FOR INSERT  
  4. AS  
  5. BEGIN  
  6.  Declare @Id int  
  7.  Select @Id = Id from inserted  
  8.   
  9.  declare @hostname varchar(30)  
  10.  select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID  
  11.    
  12.  insert into tblStaffAudit   
  13.  values('New Staff with Id  = ' + Cast(@Id as nvarchar(5)) + ' is added at ' + cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname)  
  14. END 
Insert trigger SQL description
 
Here, the name of the trigger for an insert operation is named tr_tblStaff_ForInsert on the table named tblStaff. 

Declare @Id to get one id value from tblStaff table.
  1. Declare @Id int  
  2. Select @Id = Id from inserted 
@hostname gets the value of system hostname / SQL Server hostname from the system pre-defined
object name sys.sysprocesses.
  1. declare @hostname varchar(30)  
  2. select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID 
After these parameters get the value from their respective sources, then assign those in the table named tblStaffAudit, using @Id and @hostname and get the status of the inserted records by showing an Id, date time and host name.
  1. insert into tblStaffAudit  
  2. values('New Staff with Id = ' + Cast(@Id as nvarchar(5)) + ' is added at ' + cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname) 
For getting hostname, execute the code
  1. SELECT hostname, net_library, net_address   
  2. FROM sys.sysprocesses   
  3. WHERE spid = @@SPID 
Now, insert new record and check your table, which is assigned in the trigger to show the status of the new inserted records.
  1. Insert into tblStaff values (4,'SatyaPrakash4', 2300, 'Male', 4)
Execute tblStaff table.
  1. select * from tblStaff  
 
 
Execute tblStaffAudit table.
  1. select * from tblStaffAudit 
 
 
Here, you can get the staff's Id with date time and host name, who inserted the records, using trigger.
 
New Staff with Id = 4 is added at Apr 12 2017 12:40PM Using Hostname V4UDT-09 
 
Summary
 
What is a trigger?

Types of triggers.

How to write a trigger?

Using trigger- How to get the status of the inserted records like date, time, and host name.