Trigger an action to increment all rows of an int column which are greater than or equal to the inserted row
- by Dev
I am performing some insertion to an SQL table with has three columns and several rows of data The three columns are Id,Product,ProductOrder with the following data
Id Product ProductOrder
1 Dell 1
2 HP 3
3 lenovo 2
4 Apple 10
Now, I would like a trigger which fires an action and increments all the ProductOrders by 1which are greater than or equal to the inserted ProductOrder.
For example, I am inserting a record with Id=5 Product=Sony, ProductOrder=2 Then it should look for all the products with ProductOrder greater than or equal to 2 and increment them by 1. So, the resultant data in the SQL table should be as follows
Id Product ProductOrder
1 Dell 1
2 HP 4
3 lenovo 3
4 Apple 11
5 Sony 2
From above we can see that ProductOrder which are equal or greater than the inserted are incremented by 1 like HP,Lenovo,Apple
May I know a way to implement this?
