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.

CREATE TABLE tblStaffAudit
(
 Id int identity(1,1) primary key,
 AuditData nvarchar(1000)
)

Create another table named tblStaff.

CREATE TABLE tblStaff
(
 Id int Primary Key,
 Name nvarchar(30),
 Salary int,
 Gender nvarchar(10),
 DepartmentId int
)

Now, insert some dummy records before creating insert trigger.

Insert into tblStaff values (1,'SatyaPrakash1', 5000, 'Male', 3)
Insert into tblStaff values (2,'SatyaPrakash2', 3400, 'Male', 2)
Insert into tblStaff values (3,'SatyaPrakash3', 6000, 'Male', 1)

Now, create an insert trigger.

Create TRIGGER tr_tblStaff_ForInsert
ON tblStaff
FOR INSERT
AS
BEGIN
 Declare @Id int
 Select @Id = Id from inserted

 declare @hostname varchar(30)
 select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID

 insert into tblStaffAudit
 values('New Staff with Id  = ' + Cast(@Id as nvarchar(5)) + ' is added at ' + cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname)
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.

Declare @Id int
Select @Id = Id from inserted

@hostname gets the value of system hostname / SQL Server hostname from the system pre-defined
object name sys.sysprocesses.

declare @hostname varchar(30)
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.

insert into tblStaffAudit
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

SELECT hostname, net_library, net_address
FROM sys.sysprocesses
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.

Insert into tblStaff values (4,'SatyaPrakash4', 2300, 'Male', 4)

Execute tblStaff table.

select * from tblStaff

 

 

Execute tblStaffAudit table.

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.