Home>

When there is a table like the one below, use the year and month as the key
I want to make sure that only the latest gross profit calculation NO is entered, but is it difficult to achieve by controlling only the table?
Due to circumstances, the PLSQL that is being calculated cannot be tampered with, so it is a consultation as to whether it can be managed due to table restrictions.

Current table
Gross profit calculation date Gross profit calculation NO Product code Gross profit
2020/04 1 AD1234 45
2020/04 1 AD9234 445
2020/04 2 AD1234 4545
2020/04 2 AD9234 4100

After improvement
Gross profit calculation date Gross profit calculation NO Product code Gross profit
2020/04 2 AD1234 4545
2020/04 2 AD9234 4100

create table sample
(
Gross profit calculation date varchar2 (7)
, Gross profit calculation NO number
, Product code varchar2 (6)
, Gross profit number
)

  • Answer # 1

    (Question in question comment)

    Is "calculating PLSQL" a function? Or is it a procedure?

    (Answer to question comment)

    It is a procedure. There is no primary key in this table, and the latest NO is inserted and stacked every time a calculation is performed.
    The item number is uniquely handled in the cursor.

    In the above state,

    Due to circumstances, the PLSQL that is being calculated cannot be tampered with, so it is a consultation as to whether it can be managed due to table restrictions.

    So, as a subsequent process of the stored, if the added one is the latest, you can set the trigger to delete the non-latest one.

  • Answer # 2

    When there is a table like the one below, use the year and month as the key

    Would you like to insert the data of the largest gross profit calculation NO by the gross profit calculation date and product code?

    I want to make sure that only the latest gross profit calculation NO is entered, but is it difficult to achieve by controlling only the table?

    It is easy to make appropriate comments if you can present the current INSERTed SQL or PL/SQL to the extent that there is no problem.