triggers

How to create triggers on SQL and how to use triggers?

(This study about update tiggers)

Firstly, create a database and give it a name like TRG. Then create an ITEMS table and enter 1000 line record.

Create a table named ITEMTRANSACTION for input/output operations. Then enter records (1000 line)

Create a dataset with a sub query to view the stock movement in the ITEMS table according to the input and output in the ITEMTRANSACTION table.

But writing this way is costly. We can find the cost of this operation to SQL Server by typing SET STATISTICS IO ON before SELECT:

logical reads= 748 (in the other words, 748 page is reading)

NOW IT'S TRIGGER TIME !!!

Firstly, create an ITEMSTOCK table. Enter records into this table but first clean the inside of the ITEMTRANSACTION table

TRUNCATE TABLE ITEMTRANSACTION

Then CREATE TRIGGER (INSERTED TRIGGER)

Every time we perform an INSERT operation, there will be a change first in ITEMTRANSACTION and then in ITEMSTOCK.

Run the code again, which will randomly enter 10,000 records into ITEMTRANSACTION at once.

In the other words, ITEMSTOCK table will automatically update itself when an INSERT entry is made every cycle.

At the end of the day, the reading from ITEMSTACK will be 5 and SQL's costs will be reduced.