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

Rumman Ansari   2019-03-19   Student   SQL SERVER > Create-Trigger-System-in-SQL-server   884 Share

Trigger after Insertion

Create a trigger system. When the user will insert the data from that data will insert into another table which is reserved for the backup.

Use Database 

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

This is our primary table where data will refelect

<span class="pln">
CREATE TABLE </span><span class="typ">Product_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 is used for the backup information

<span class="pln">
CREATE TABLE </span><span class="typ">ProductLog_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 below section we are creating a trigger, which will store the data after insertion in the first table. 

<span class="pln">
CREATE TRIGGER </span><span class="typ">TriggerpRODUCT_1637935</span><span class="pln"> ON </span><span class="typ">Product_1637935</span><span class="pln">
AFTER INSERT
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_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">'Added'</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>

after this insert command data also intert in the log table (backup table) 

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

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_1637935</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">ProductLog_1637935</span><span class="pln">
</span>
Share this Post: