/* 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 and sync employee_backup table with records of employee table so both 
  table will have same records */

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

SELECT * 
INTO  EMPLOYEE_BACKUP
FROM  EMPLOYEE
GO

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

SELECT * from EMPLOYEE
SELECT * from EMPLOYEE_BACKUP
GO


/* After Insert trigger on employee table */

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

CREATE TRIGGER TRG_InsertSyncEmp 
ON dbo.EMPLOYEE
AFTER INSERT AS
BEGIN
INSERT INTO EMPLOYEE_BACKUP
SELECT * FROM INSERTED
END
GO

/* Insert a record in Employee table Insert trigger will be executed here 
   and same record will be inserted into employee_backup table */

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME) VALUES (504, 'Vish', 'Dalvi');

/* See both the tables both tables are sync with same number of records. */

SELECT * from EMPLOYEE
SELECT * from EMPLOYEE_BACKUP 
GO