Delete Audit Trigger in SQL Server

Had a need to find out who/what is deleting rows from a table, this trigger will fire on any delete and save the info to a custom table that is based on the good old sp_who3 results,
Here we just want to know about the SPID of the trigger connection, we then get the current sql via DBCC INPUTBUFFER (if any) and then get connection info and save it to a table!

Credit goes to Shailendra Chauhan
End result is something like this:

create TRIGGER trgAuditDelete ON dbo.Employee_Demo
FOR DELETE
AS
/*

drop trigger trgAuditDelete
disable trigger trgAuditDelete ON dbo.Employee_Demo
enable trigger trgAuditDelete ON dbo.Employee_Demo

use master
go

CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)

-- Now Insert records
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
select * from Employee_Demo

Unit Test
begin tran
--Output will be
 --Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where emp_id = 5
--Output will be
select * from sp_Who3Results
rollback tran

begin tran
--Output will be
 --Now try to delete data in Employee_Demo table
DELETE Employee_Demo
--Output will be
select * from sp_Who3Results
rollback tran

begin tran
--Output will be
 --Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where 5= 6
--Output will be
select * from sp_Who3Results
rollback tran


 --Now try to delete data in Employee_Demo table
truncate table Employee_Demo
--Output will be
select * from sp_Who3Results

*/
begin

DECLARE @tBuff nvarchar(2000), @sql nvarchar(100)
SET @tBuff = ''

PRINT 'trgAfterDelete() INPUTBUFFER, spid ->' + cast(@@Spid as varchar(50))
begin try
Set @sql = 'DBCC INPUTBUFFER (' + cast(@@Spid as varchar(50)) + ')'
CREATE TABLE #tempBuf (   EventType nvarchar(2000), Parameters nvarchar(2000), EventInfo nvarchar(2000))
INSERT INTO #tempBuf EXECUTE (@sql)

SELECT TOP 1 @tBuff = left(EventInfo, 2000) FROM #tempBuf
PRINT  'trgAfterDelete() @tBuff -> ' + @tBuff
DROP TABLE #tempBuf
end try begin catch end catch
PRINT 'trgAfterDelete() INPUTBUFFER DONE, spid ->' + cast(@@Spid as varchar(50))


PRINT 'trgAfterDelete() insert into sp_Who3Results, spid ->' + cast(@@Spid as varchar(50))
begin try
insert into sp_Who3Results
SELECT sp.spid,
    'TRIGGER' status,
    sp.loginame,
    nullif(rtrim(ltrim(sp.hostname)), '') hostname,
    sp.blocked,
    sd.name,
    sp.cmd,
    sp.cpu,
    sp.physical_io,
    sp.memusage,
    sp.open_tran,
    sp.last_batch,
    sp.login_time,
    nullif(rtrim(ltrim(sp.program_name)), '') Programname,
@tBuff eventInfo,
getdate() DateTimeCreated
--into sp_Who3ResultsTrigger
    FROM master..sysprocesses sp
    LEFT OUTER JOIN master..sysdatabases sd ON sd.dbid = sp.dbid
Where sp.spid = @@SPID
PRINT 'trgAfterDelete() insert into sp_Who3Results DONE, spid ->' + cast(@@Spid as varchar(50))

end try begin catch end catch


PRINT 'AFTER DELETE TRIGGER fired.'
end