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

Continue>> 

 

DELETE Trigger Syntax

Create TRIGGER tr_tblStaff_ForDelete
ON tblStaff
FOR DELETE
AS
BEGIN
 Declare @Id int
 Select @Id = Id from deleted

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

 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)
 END

Delete Trigger Syntax Description

 
Declare @Id that one get id value from "tblStaff" table. 

Declare @Id int
Select @Id = Id from deleted

and @hostname get value of system hostname / sql server hostname from system pre-defined
object named "sys.sysprocesses".

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

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.

delete from tblstaff where id = 3

 Check The Table Data.

select * from tblstaff

 

 

Then Check The Status of Deleted Records using this table. 

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.