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

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

Trigger After Update

Create a trigger system. When user will update 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 Updated.

Database Name


USE DB02TEST01

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


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

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


CREATE TABLE ProductLog_RAS_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. Also In this section you have to remember that you should to use the word AFTER DELETE 


ALTER TRIGGER BALLupdate ON Product_RAS_1637935
AFTER UPDATE
AS
BEGIN
DECLARE @ProductId int
DECLARE @ProductName varchar(50)
DECLARE @Price Money
SET @ProductId = (SELECT I.ProductId FROM inserted I)
SET @ProductName = (SELECT I.ProductName FROM inserted I)
SET @Price = (SELECT I.Price FROM inserted I)
INSERT INTO ProductLog_RAS_1637935 VALUES (@ProductId, @ProductName, @Price, 'Updated',
getdate())
END

Before Updation the table should have some data inside table. So insert one row in the primary table


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

SELECT * FROM Product_RAS_1637935
SELECT * FROM ProductLog_RAS_1637935

After deletion of the information trigger will work. It will insert 1 more extra row 


UPDATE Product_RAS_1637935 SET ProductName = 'Sugar' WHERE ProductId = 100

See the information in both the table


SELECT * FROM Product_RAS_1637935
SELECT * FROM ProductLog_RAS_1637935
Share this Post: