Create a trigger system in SQL Server (Trigger After Deletion)

Rumman Ansari   2019-03-19   Student   SQL SERVER > Trigger-After-Deletion   8102 Share

Trigger After Deletion

Create a trigger system. When the user will delete the data from the table the data will insert into another table which is reserved for the backup and it should show the track information as Deleted.

Database Name


USE DB02TEST01

Create a primary table which will store the basic information of the products: 


CREATE TABLE Product_RA_1637935(
ProductId int,
ProductName varchar(50),
Price Money
)

This table will store the product information after deletion of the data from the primary table 


CREATE TABLE ProductLog_RA_1637935(
ProductId int,
ProductName varchar(50),
Price Money,
Track varchar(50),
AuditTime datetime
)

In this section we are creating the TRIGGER for storing the information inside the log table. You have to remember that you should use the word AFTER DELETE 


CREATE TRIGGER BALL ON Product_RA_1637935
AFTER DELETE
AS
BEGIN
DECLARE @ProductId int
DECLARE @ProductName varchar(50)
DECLARE @Price Money
SET @ProductId = (SELECT I.ProductId FROM deleted I)
SET @ProductName = (SELECT I.ProductName FROM deleted I)
SET @Price = (SELECT I.Price FROM deleted I)
INSERT INTO ProductLog_RA_1637935 VALUES (@ProductId, @ProductName, @Price, 'Deleted',
getdate())
END

To delete from the table you should have some data inside the table. for that insert one single row in the primary table.


INSERT INTO Product_RA_1637935 VALUES(100, 'Rice',100)

After deletion of the information trigger will work. It will insert 1 more extra row in side the second table


DELETE FROM Product_RA_1637935 WHERE ProductId = 100

See the information on both the table 


SELECT * FROM Product_RA_1637935
SELECT * FROM ProductLog_RA_1637935
Share this Post: