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

Rumman Ansari   2019-03-19   Student   SQL SERVER > Trigger-After-Deletion   785 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

<span class="pln">
USE DB02TEST01
</span>

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

<span class="pln">
CREATE TABLE </span><span class="typ">Product_RAS_1637935</span><span class="pun">(</span><span class="pln">
</span><span class="typ">ProductId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
</span><span class="typ">ProductName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
</span><span class="typ">Price</span><span class="pln"> </span><span class="typ">Money</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

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

<span class="pln">
CREATE TABLE </span><span class="typ">ProductLog_RAS_1637935</span><span class="pun">(</span><span class="pln">
</span><span class="typ">ProductId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
</span><span class="typ">ProductName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
</span><span class="typ">Price</span><span class="pln"> </span><span class="typ">Money</span><span class="pun">,</span><span class="pln">
</span><span class="typ">Track</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
</span><span class="typ">AuditTime</span><span class="pln"> datetime
</span><span class="pun">)</span><span class="pln">
</span>

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 

<span class="pln">
ALTER TRIGGER </span><span class="typ">BALLupdate</span><span class="pln"> ON </span><span class="typ">Product_RAS_1637935</span><span class="pln">
AFTER UPDATE
AS
</span><span class="kwd">BEGIN</span><span class="pln">
DECLARE </span><span class="lit">@ProductId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln">
DECLARE </span><span class="lit">@ProductName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln">
DECLARE </span><span class="lit">@Price</span><span class="pln"> </span><span class="typ">Money</span><span class="pln">
SET </span><span class="lit">@ProductId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">SELECT I</span><span class="pun">.</span><span class="typ">ProductId</span><span class="pln"> FROM inserted I</span><span class="pun">)</span><span class="pln">
SET </span><span class="lit">@ProductName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">SELECT I</span><span class="pun">.</span><span class="typ">ProductName</span><span class="pln"> FROM inserted I</span><span class="pun">)</span><span class="pln">
SET </span><span class="lit">@Price</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">SELECT I</span><span class="pun">.</span><span class="typ">Price</span><span class="pln"> FROM inserted I</span><span class="pun">)</span><span class="pln">
INSERT INTO </span><span class="typ">ProductLog_RAS_1637935</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">@ProductId</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@ProductName</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@Price</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Updated'</span><span class="pun">,</span><span class="pln">
getdate</span><span class="pun">())</span><span class="pln">
</span><span class="kwd">END</span><span class="pln">
</span>

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

<span class="pln">
INSERT INTO </span><span class="typ">Product_RAS_1637935</span><span class="pln"> VALUES</span><span class="pun">(</span><span class="lit">100</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rice'</span><span class="pun">,</span><span class="lit">100</span><span class="pun">)</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Product_RAS_1637935</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">ProductLog_RAS_1637935</span><span class="pln">
</span>

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

<span class="pln">
UPDATE </span><span class="typ">Product_RAS_1637935</span><span class="pln"> SET </span><span class="typ">ProductName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Sugar'</span><span class="pln"> WHERE </span><span class="typ">ProductId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">100</span><span class="pln">
</span>

See the information in both the table

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Product_RAS_1637935</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">ProductLog_RAS_1637935</span><span class="pln">
</span>
Share this Post: