We have a pricing table, with fields as shown below.
The price of a material changes with time. So during calculation purpose , we have to consider current date, and find the corresponding Item where this current date is in between Valid date Range based on fromDate and ToDate in the table.
When am doing calculation today i.e. 11th SEP 2020 , then valid price i have to consider is second line item where price is£ 110 .
when am doing the calculation on 15th December of 2020, then Valid Price of the material i have to consider is the Third Line Item where Price is £ 90 .
we have inserted a column VALID of type varchar with length as 1, which holds value 'Y' or 'N' . so the Valid Column will have value Y , for the first line Item during 2020.01.01 to 2020.04.30. so during calculations we wanted to query the table with material number and value of Valid as Y .
SELECT PRICE FROM PRICE_TABLE WHERE MATERIAL_CODE = 'ZM100' AND VALID = 'Y' , Which will fetch me valid price.
Is it possible to have an SQL Trigger, which will
1)Insert value Y in VALID Column for the First Row at 12:00 AM on date 2020.01.01 & Value N for other Rows.
2)Insert value Y in VALID Column for the Second Row at 12:00 AM on date 2020.05.01 & Value N for other Rows.
3)Insert value Y in VALID Column for the Third Row at 12:00 AM on date 2020.12.01 & Value N for other Rows.