SQL Server Trigger - Insert the deleted record into another table with deletetime

 SQL Server Trigger - Insert the deleted record into another table with deletetime

/*
Author: Tanveer Haider Baig
Description : Trigger to fire after delete on table and populate deleted records to another table with timestamp
*/


--Create Primary table
CREATE TABLE tblTrgTest1
(
id INT,
NAME VARCHAR(10)
);
--=== Populating Table
INSERT INTO tblTrgTest1
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'d'
UNION ALL
SELECT 5,'e';
--=== Retrivening Results
SELECT * FROM tblTrgTest1;

--===== Create secondary table to host deleted values
CREATE TABLE tblTrgTest2
(
id INT,
NAME VARCHAR(10),
delDate DATETIME
);

--===== Create trigger to fire on primary table delete
CREATE TRIGGER trgTableDeleteLog
ON tblTrgTest1
AFTER DELETE
AS
INSERT INTO tblTrgTest2
SELECT * ,GETDATE()
FROM DELETED;

---===== delete records to test trigger
DELETE FROM tblTrgTest1 WHERE id IN (1,2);
DELETE FROM tblTrgTest1 WHERE id IN (3,5);

--Verify deleted records come to secondry table with timestamp
SELECT * FROM tblTrgTest2;

--Verify delete was successfull on primary
SELECT * FROM tblTrgTest1;

--=== Drop objects
DROP TRIGGER trgTableDeleteLog;
DROP TABLE tblTrgTest1;
DROP TABLE tblTrgTest2;