I have never written triggers before, so would apprecite any helpor suggestion on this.
In my database I have 2 tables:
Master table (Orders)
What I want is:
1) Insert new priority:
when a status of an order is changed to "In Progress" in the master table, a new record should be added to the priority with a defualt low priority based on the department. Ex.if Priority table has 5 orders for department 001,then the order should be added with priority 6
when a status of an order is changed from "In Progress" to something else in the master table, then delete its priority in the Priority table and readjust the priorities for the related department. Ex.if Priority table has 5 orders for department 001,and the order for which the status got changed has priority of 3, then, delete this record and readjust the priority for the subsequent orders in that department.
Currently I am doing this through code, but hope that this can be managed within the database without putting extra coding load as I have many department and orders that needs to be maintained in such a way.