Delete Trigger To Find Out Which Host Name Delete Records In What Date Time

Instruction & Direction

Before Going through this session Read My Previous Blog >>

Using Trigger In Sql Server Find Out Date Time & Host Name who insert records 
Continue>> 

Update Trigger To Find Out Which Host Name Update Records In What Date Time
 
DELETE Trigger Syntax
  1. Create TRIGGER tr_tblStaff_ForDelete  
  2. ON tblStaff  
  3. FOR DELETE  
  4. AS  
  5. BEGIN  
  6.  Declare @Id int  
  7.  Select @Id = Id from deleted  
  8.   
  9.  declare @hostname varchar(30)    
  10.  select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID   
  11.    
  12.  insert into tblStaffAudit   
  13.  values('An Existing Staff With Id  = ' + Cast(@Id as nvarchar(5)) + ' Is deleted at ' + Cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname)  
  14.  END 
Delete Trigger Syntax Description
 
Declare @Id that one get id value from "tblStaff" table. 
  1. Declare @Id int  
  2. Select @Id = Id from deleted  
and @hostname get value of system hostname / sql server hostname from system pre-defined
object named "sys.sysprocesses".
  1. declare @hostname varchar(30)    
  2. select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID  
After these parameters get value from their respected sources then assign those in table named "tblStaffAudit" using @Id and @hostname and get the status of deleted records by showing Id , Date Time and Host Name.
  1. insert into tblStaffAudit  values('An Existing Staff With Id  = ' + Cast(@Id as nvarchar(5)) + ' Is deleted at ' + Cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname) 
Difference Between INSERTED table , UPDATED table , DELETED table
 
INSERTED table, is a special table used by DML triggers. When you add a new row into tblStaff table,
a copy of the row will also be made into inserted table, which only a trigger can access.
You cannot access this table outside the context of the trigger. The structure of the inserted table will be identical to the structure of tblStaff table.

Updated table, is a special table used by DML triggers. When you update the existing row and update row
using specific Id and push data into tblStaff table. a copy of the update row will also be made into updated table, which only a trigger can access.You cannot access this table outside the context of the trigger.
The structure of the Updated table will be identical to the structure of tblStaff table.

The triggering event as DELETE and fetch the deleted row ID from DELETED table. DELETED table, is
a table used by DML triggers. When you delete a row from tblStaff table, a copy of the deleted row will be made available in DELETED table which only a trigger can access. Just like INSERTED table, DELETED table cannot be accessed, outside the context of the trigger and, the structure of the DELETED table will be identical to the structure of tblStaff table.

Now Delete one record.
  1. delete from tblstaff where id = 3 
 Check The Table Data.
  1. select * from tblstaff 
 
 
Then Check The Status of Deleted Records using this table. 
  1. select * from tblStaffAudit 
 
 
Status will be shown like this:
 
An Existing Staff With Id = 3 Is deleted at Apr 12 2017 3:33PM Using Hostname V4UDT-09
 
SUMMARY
 
How to write Delete Trigger.

Using Delete Trigger How to get status of deleted records like Date Time and Host Name. 
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now