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

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

<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_RA_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 deletion of the data from the primary table 

<span class="pln">
CREATE TABLE </span><span class="typ">ProductLog_RA_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. You have to remember that you should use the word AFTER DELETE 

<span class="pln">
CREATE TRIGGER BALL ON </span><span class="typ">Product_RA_1637935</span><span class="pln">
AFTER DELETE
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 deleted 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 deleted 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 deleted I</span><span class="pun">)</span><span class="pln">
INSERT INTO </span><span class="typ">ProductLog_RA_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">'Deleted'</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>

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

<span class="pln">
INSERT INTO </span><span class="typ">Product_RA_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">
</span>

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

<span class="pln">
DELETE FROM </span><span class="typ">Product_RA_1637935</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 on both the table 

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