/* Check for table employee already exists or not*/

IF OBJECT_ID('EMPLOYEE') IS NOT NULL
DROP TABLE EMPLOYEE
GO

/* Create Employee table if not exists*/

CREATE TABLE EMPLOYEE
(
 EMPID INT PRIMARY KEY,
 FNAME VARCHAR(25),
 LNAME VARCHAR(25),
)
GO

/*Populate Employee table with sample rows*/

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (500, 'John','Smith'),
      (501, 'Alex','Admas'),
      (502, 'Eric','James'),
      (503, 'Shaun','Marsh')
GO

/*Create a Employee_History table same structure as
Employee table with no rows*/

IF OBJECT_ID('EMPLOYEE_HISTORY') IS NOT NULL
DROP TABLE EMPLOYEE_HISTORY
GO

SELECT *
INTO  EMPLOYEE_HISTORY
FROM  EMPLOYEE
WHERE 1 = 0 -- This will populate Employee_history table with 0 rows
GO

/*See table records from both tables we've 4 rows in both tables*/

SELECT * from EMPLOYEE e -- 4 rows
SELECT * from EMPLOYEE_HISTORY eh -- 0 rows

/*After Delete trigger on employee table*/

IF OBJECT_ID('TRG_EmployeeHistory') IS NOT NULL
DROP TRIGGER TRG_EmployeeHistory
GO

CREATE TRIGGER TRG_EmployeeHistory
ON dbo.EMPLOYEE
AFTER DELETE AS
BEGIN
INSERT INTO EMPLOYEE_HISTORY
SELECT * FROM DELETED
END
GO

/*Delete a record from Employee table*/

DELETE FROM EMPLOYEE
WHERE EMPID = 501
GO

/*See both the tables, deleted record from employee is inserted into Employee_HISTORY*/

SELECT * from EMPLOYEE  -- 3 rows
SELECT * from EMPLOYEE_HISTORY -- 1 row
GO