How to audit Delete action in SQL Server?

Question

In SQL Server, I have created an Employee table as shown below, I want to record all delete actions to another history table 

Ex: when I delete a new record on the main table, it takes a copy from this record to another history table with the deleted date and time and who delete this record?

So How can use triggers In SQL Server to record all delete actions to a history table?

Answer 1

To audit Delete action in SQL Server, you can use Trigger on delete

Trigger: is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires.

There are three types of trigger :

• Data manipulation language (DML) triggers which are invoked automatically in response to INSERT, UPDATE, and DELETE events against tables.
• Data definition language (DDL)triggers which fire in response to CREATE, ALTER, and DROP statements. DDL triggers also fire in response to some system stored procedures that perform DDL-like operations.
• Logon triggers which fire in response to LOGON events
  SQL Server provides two virtual tables that are available specifically for triggers called INSERTED and DELETED tables. SQL Server uses these tables to capture the data of the modified row before and after the event occurs.

To record data create a new table EmployeeHistory and create Trigger which is fired when deleting a record from the Employee table

--EmployeeHistory Table
CREATE TABLE EmployeeHistory (
	ID int IDENTITY(1,1) NOT NULL,
	AuditData nvarchar(100) NULL
)

--Tr_Employee_For_Delete Trigger
Create Trigger Tr_Employee_For_Delete
on Employee
For delete
as 
begin
	Declare @id int

	Select @id= id from deleted

	insert into EmployeeHistory values 
	('An existing employee with id =' + 
	cast (@id as nvarchar(5)) + 
	'is deleted at ' + cast(getdate() as nvarchar(20) )
	)

end