Hi
I would like to know the best way to close the validity period between dates, when I include another line and I have to update by closing on the previous date
CREATE TABLE TMP_VALIDITY_PERIOD
(
CODE VARCHAR2(6 BYTE),
DT_INI DATE,
DT_FIN DATE,
TX_VALUE NUMBER
)
CREATE UNIQUE INDEX TMP_VALIDITY_PERIOD_U01 ON TMP_VALIDITY_PERIOD (CODE, DT_INI);
ALTER TABLE TMP_VALIDITY_PERIOD ADD (
CONSTRAINT TMP_VALIDITY_PERIOD_U01
UNIQUE (CODE, DT_INI)
USING INDEX TMP_VALIDITY_PERIOD_U01
ENABLE VALIDATE);
Insert into TMP_VALIDITY_PERIOD (CODE, DT_INI, TX_VALUE) Values ('000001', TO_DATE('01/01/2022', 'DD/MM/YYYY'), 100);
Insert into TMP_VALIDITY_PERIOD (CODE, DT_INI, TX_VALUE) Values ('000002', TO_DATE('01/02/2022', 'DD/MM/YYYY'), 200);
Insert into TMP_VALIDITY_PERIOD (CODE, DT_INI, TX_VALUE) Values ('000003', TO_DATE('01/01/2022', 'DD/MM/YYYY'), 250);
Insert into TMP_VALIDITY_PERIOD (CODE, DT_INI, TX_VALUE) Values ('000004', TO_DATE('01/01/2022', 'DD/MM/YYYY'), 300);
COMMIT;
When I insert other row same code in other date , I would like to do update in column DT_FIN with same CODE , example
I have CODE 000001 DT_INI IS 01/01/2022 , if I insert other row with this code (000001) with other date , example :
Insert into TMP_VALIDITY_PERIOD (CODE, DT_INI, TX_VALUE) Values ('000001', TO_DATE('14/10/2022', 'DD/MM/YYYY'), 200);
I would like to update in the previous row that has the same code, the update should be the day of the date I'm including, minus 1 day
like
UPDATE TMP_VALIDITY_PERIOD
SET DT_FIN = TO_DATE('13/10/2022','DD/MM/YYYY')
WHERE CODE = '000001'
AND DT_INI = TO_DATE('01/01/2022';
What's the best way to do this, would be using trigger ?
I would like to know the best way to close the validity period between dates, when I include another line and I have to update by closing on the previous date
using
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production