Use triggers in SQL via basic examples
- Tram Ho
What is a trigger?
Simply put, the trigger is a stored procedure with no parameters. The trigger executes automatically when one of the three Insert, Update and Delete statements changes the data on the trigger table. ?
Trigger’s syntax
1 | <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> tên_trigger <span class="token keyword">ON</span> tên_bảng <span class="token keyword">FOR</span> { <span class="token keyword">DELETE</span> <span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> } <span class="token keyword">AS</span> câu_lệnh_sql |
What does trigger use?
- Triggers are often used to check constraints on multiple relationships (multiple tables / tables) or on multiple lines (multiple records) of the table.
- In addition to using Trigger, the program has implicit functions to serve but finite cases and is often not used for business or transaction purposes. Read more here
Problem set.
- You have 2 tables of warehouses and orders linked together by item code.
- When the user orders, automatically update the inventory number.
Solution
- When users order, we only have 3 main types of operations with the database: Insert, Delete, Update
- So just creating 3 corresponding triggers is ok ?
- User ordered : Quantity left in stock = Quantity left – Number of orders
- User cancels no order: Quantity left in stock = Quantity left + Number of orders
- User update Set quantity => Quantity also increases or decreases arbitrarily
Problem
- In two cases insert and delete we do normally. But in case of updating the quantity of inventory will occur in the following 3 cases.
- The initial set amount = 5 then increases to 10 => the number in stock will decrease by 10 respectively
- The number placed at this time = 10 then reduced to 3 => the number in stock will increase by 7 respectively
- Take advantage of update statement sql = Insert new row To Delete old row means that when updating database in sql, it will run the insertion of new data first and then delete the old table.
Problem solving
- Taking advantage of using a Trigger that always has two inserted and deleted tables, we will draw a medium update formula in all cases
1 | SLTonKhoCu = SLTonKhoCu - inserted.SLDatHang + deleted.SLDatHang |
Maybe you want to see:
Optimize WHERE statements in MySQL
Introducing LevelDB and comparing performance with MySQL
Follow the small example
- Initially add data and select it ?
- Order 5 products with code 1
- Update to 10
- Update on 3
- Update some other information that is not related to quantity
- Delete orders
Source code all ?
- Additional trigger
- Trigger Delete
- Trigger Edit
1 | <span class="token comment">/* cập nhật hàng trong kho sau khi đặt hàng hoặc cập nhật */</span> <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> trg_DatHang <span class="token keyword">ON</span> tbl_DatHang <span class="token keyword">AFTER</span> <span class="token keyword">INSERT</span> <span class="token keyword">AS</span> <span class="token keyword">BEGIN</span> <span class="token keyword">UPDATE</span> tbl_KhoHang <span class="token keyword">SET</span> SoLuongTon <span class="token operator">=</span> SoLuongTon <span class="token operator">-</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> SoLuongDat <span class="token keyword">FROM</span> inserted <span class="token keyword">WHERE</span> MaHang <span class="token operator">=</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token punctuation">)</span> <span class="token keyword">FROM</span> tbl_KhoHang <span class="token keyword">JOIN</span> inserted <span class="token keyword">ON</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token operator">=</span> inserted <span class="token punctuation">.</span> MaHang <span class="token keyword">END</span> GO <span class="token comment">/* cập nhật hàng trong kho sau khi cập nhật đặt hàng */</span> <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> trg_CapNhatDatHang <span class="token keyword">on</span> tbl_DatHang <span class="token keyword">after</span> <span class="token keyword">update</span> <span class="token keyword">AS</span> <span class="token keyword">BEGIN</span> <span class="token keyword">UPDATE</span> tbl_KhoHang <span class="token keyword">SET</span> SoLuongTon <span class="token operator">=</span> SoLuongTon <span class="token operator">-</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> SoLuongDat <span class="token keyword">FROM</span> inserted <span class="token keyword">WHERE</span> MaHang <span class="token operator">=</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> SoLuongDat <span class="token keyword">FROM</span> deleted <span class="token keyword">WHERE</span> MaHang <span class="token operator">=</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token punctuation">)</span> <span class="token keyword">FROM</span> tbl_KhoHang <span class="token keyword">JOIN</span> deleted <span class="token keyword">ON</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token operator">=</span> deleted <span class="token punctuation">.</span> MaHang <span class="token keyword">end</span> GO <span class="token comment">/* cập nhật hàng trong kho sau khi hủy đặt hàng */</span> <span class="token keyword">create</span> <span class="token keyword">TRIGGER</span> trg_HuyDatHang <span class="token keyword">ON</span> tbl_DatHang <span class="token keyword">FOR</span> <span class="token keyword">DELETE</span> <span class="token keyword">AS</span> <span class="token keyword">BEGIN</span> <span class="token keyword">UPDATE</span> tbl_KhoHang <span class="token keyword">SET</span> SoLuongTon <span class="token operator">=</span> SoLuongTon <span class="token operator">+</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> SoLuongDat <span class="token keyword">FROM</span> deleted <span class="token keyword">WHERE</span> MaHang <span class="token operator">=</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token punctuation">)</span> <span class="token keyword">FROM</span> tbl_KhoHang <span class="token keyword">JOIN</span> deleted <span class="token keyword">ON</span> tbl_KhoHang <span class="token punctuation">.</span> MaHang <span class="token operator">=</span> deleted <span class="token punctuation">.</span> MaHang <span class="token keyword">END</span> |
Conclude
The thing that you use Trigger is not mandatory and we often think that no one uses it completely wrong ?. But Trigger, as far as I know, there are still many places that will use it for their own purposes.
Thanks for reading.
Filter customer Insights with data – the decision maker’s decision factor
Use triggers in SQL via basic examples
Analyzing Northern lottery data ”]