Przechowywanie podsumowanych zmian w tabeli z logami

Cześć,

mam taki problem, chodzi o bazy Oracle. Otóż w tabeli logów tLIJobsLogs, w kolumnie ChangeDescription chciałbym przechowywać dane w postaci
[NazwaKolumny] [StaraWartość] [NowaWartość]
[NazwaKolumny]...
Wiem, że teoretycznie wszystkie takie informacje będą znajdowały się już w tabeli, ale chciałbym coś takiego zrobić do nauki.
I teraz tutaj pojawia się moje pytanie, czy da się to zrobić bez hardkowdowania listy i nazwy kolumn? Bo teoretycznie mógłbym to normalnie wypisać, ale np jakbym dodał kolumne do tabeli tEMJobs to chciałby, żeby kod automatycznie uwzględniał zmiany w tej tabeli. Aktualny mój kod:

Tabela tranzaykcyjna

CREATE TABLE tEMJobs (
    ID NUMBER GENERATED ALWAYS AS IDENTITY (
        START WITH 1 INCREMENT BY 1
    ) 
                                                                CONSTRAINT PK_tEMJobs                      PRIMARY KEY
,   JobName              VARCHAR2(50 CHAR)                      CONSTRAINT NN_tEMJobs_JobName              NOT NULL
,   MinSalary            NUMBER(9, 2)                           CONSTRAINT NN_tEMJobs_MinSalary            NOT NULL
,   MaxSalary            NUMBER(9, 2)                           CONSTRAINT NN_tEMJobs_MaxSalary            NOT NULL
,   ValidFrom            DATE             DEFAULT SYSDATE       CONSTRAINT NN_tEMJobs_ValidFrom            NOT NULL
,   ValidTo              DATE
,   ISVALID              VARCHAR2(1 CHAR)                       CONSTRAINT NN_tEMJobs_ISVALID              NOT NULL
,   LastModificationDate TIMESTAMP(4)     DEFAULT SYSTIMESTAMP  CONSTRAINT NN_tEMJobs_LastModificationDate NOT NULL
,   CONSTRAINT UNQ_tEMJobs_JobName UNIQUE (JobName)
,   CONSTRAINT CHK_tEMEJobs_CheckDateOfValid CHECK (ValidFrom < ValidTo)
,   CONSTRAINT CHK_tEMEJobs_ValueOfISVALID CHECK (ISVALID IN ('Y', 'N'))
,   CONSTRAINT CHK_tEMEJobs_FirstCapitalLetterInJobName CHECK (REGEXP_LIKE(JobName, '^[A-Z]'))
);

Tabela przechowująza zmiany na tabeli tEMJobs

CREATE TABLE tLIJobsLogs (
    ID NUMBER GENERATED ALWAYS AS IDENTITY (
        START WITH 1 INCREMENT BY 1
    )                                                              CONSTRAINT PK_tLIJobsLogs                   PRIMARY KEY
,   Job_ID               NUMBER(4, 0)                              CONSTRAINT NN_tLIJobsLogs_JobID             NOT NULL
,   JobName              VARCHAR2(50 CHAR)                         CONSTRAINT NN_tLIJobsLogs_JobName           NOT NULL
,   MinSalary            NUMBER(9, 2)                              CONSTRAINT NN_tLIJobsLogs_MinSalary         NOT NULL
,   MaxSalary            NUMBER(9, 2)                              CONSTRAINT NN_tLIJobsLogs_MaxSalary         NOT NULL
,   ValidFrom            DATE                DEFAULT SYSDATE       CONSTRAINT NN_tLIJobsLogs_ValidFrom         NOT NULL
,   ValidTo              DATE
,   ISVALID              VARCHAR2(1 CHAR)                          CONSTRAINT NN_tLIJobsLogs_ISVALID           NOT NULL
,   ChangeMadeBy         VARCHAR2(50 CHAR)                         CONSTRAINT NN_tLIJobsLogs_ChangeMadeBy      NOT NULL
,   DateOfChange         TIMESTAMP(4)        DEFAULT SYSTIMESTAMP  CONSTRAINT NN_tLIJobsLogs_DateOfChange      NOT NULL
,   ChangeDescription    CLOB                                      CONSTRAINT NN_tLIJobsLogs_ChangeDescription NOT NULL
,   CONSTRAINT FK_tLIJobsLogs_JobID_TO_tEMJobs_ID FOREIGN KEY (Job_ID)
               REFERENCES tEMJobs(ID)
,   CONSTRAINT CHK_tLIJobsLogs_CheckDateOfValid CHECK (ValidFrom < ValidTo)
,   CONSTRAINT CHK_tLIJobsLogs_ValueOfISVALID CHECK (ISVALID IN ('Y', 'N'))
,   CONSTRAINT CHK_tLIJobsLogs_FirstCapitalLetterInJobName CHECK (REGEXP_LIKE(JobName, '^[A-Z]'))
)
PARTITION BY LIST (ISVALID)
SUBPARTITION BY RANGE (ValidFrom)
(   PARTITION tLIJobsLogs_Valid VALUES ('Y')
        (SUBPARTITION tLIJobsLogs_Valid_before_1990 VALUES LESS THAN (TO_DATE('01-JAN-1990', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_Valid_before_1995 VALUES LESS THAN (TO_DATE('01-JAN-1995', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_Valid_before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_Valid_before_2005 VALUES LESS THAN (TO_DATE('01-JAN-2005', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_Valid_before_2010 VALUES LESS THAN (TO_DATE('01-JAN-2010', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_Valid_before_2015 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_Valid_before_2020 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))
        )
,   PARTITION tLIJobsLogs_NoValid VALUES ('N')
        (SUBPARTITION tLIJobsLogs_NoValid_before_1990 VALUES LESS THAN (TO_DATE('01-JAN-1990', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_NoValid_before_1995 VALUES LESS THAN (TO_DATE('01-JAN-1995', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_NoValid_before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_NoValid_before_2005 VALUES LESS THAN (TO_DATE('01-JAN-2005', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_NoValid_before_2010 VALUES LESS THAN (TO_DATE('01-JAN-2010', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_NoValid_before_2015 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY'))
        ,SUBPARTITION tLIJobsLogs_NoValid_before_2020 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))
        )
)
ENABLE ROW MOVEMENT;

Kod wyzwalacza

CREATE OR REPLACE EDITIONABLE TRIGGER trgDMLtEMJobs_logs
AFTER INSERT OR UPDATE ON tEMJobs
REFERENCING OLD AS old_record NEW AS new_record
FOR EACH ROW
ENABLE
DECLARE
    CURSOR cur_ListOftEMJobsColumns IS
        SELECT   column_name
        FROM     user_tab_columns
        WHERE    table_name = UPPER('tEMJobs')
        ORDER BY column_id ASC;

    vtab_ListOftEMJobsColumnsName PACKSMPLSQLTYPES.TABNAMESOFCOLUMNTABLE := PACKSMPLSQLTYPES.TABNAMESOFCOLUMNTABLE();
    vc_ChangeDescription          TLIJOBSLOGS.CHANGEDESCRIPTION%TYPE;
BEGIN

    OPEN cur_ListOftEMJobsColumns;

    FETCH cur_ListOftEMJobsColumns
    BULK COLLECT INTO vtab_ListOftEMJobsColumnsName;

     
    IF NOT cur_ListOftEMJobsColumns%ROWCOUNT <> 0 THEN
        RAISE packSMPLSQLTypes.ge_noDataFoundInMetaView;        
    END IF;

    CLOSE cur_ListOftEMJobsColumns;

    DBMS_LOB.CREATETEMPORARY(vc_ChangeDescription
                            ,TRUE
                            ,DBMS_LOB.CALL);

      
    
    CASE 
    
        WHEN INSERTING THEN

            FOR indexOfTab IN vtab_ListOftEMJobsColumnsName.FIRST .. vtab_ListOftEMJobsColumnsName.LAST LOOP 
                vc_ChangeDescription := vc_ChangeDescription || 
                                        '[' || vtab_ListOftEMJobsColumnsName(indexOfTab) || ']' || ' ' ||
                                        '[' || :new_record.JobName || ']';                                    
            END LOOP;

            INSERT INTO tLIJobsLogs(Job_ID
                                   ,JobName
                                   ,MinSalary
                                   ,MaxSalary
                                   ,ValidFrom
                                   ,ValidTo
                                   ,ISVALID
                                   ,ChangeMadeBy
                                   ,DateOfChange
                                   ,ChangeDescription
                                   )
            VALUES (:new_record.ID
                   ,:new_record.JobName
                   ,:new_record.MinSalary
                   ,:new_record.MaxSalary
                   ,DEFAULT
                   ,NULL
                   ,'Y'
                   ,SYS_CONTEXT('USERENV', 'CURRENT_USER')
                   ,DEFAULT
                   ,vc_ChangeDescription);

        WHEN UPDATING THEN

            IF :new_record.ValidTo IS NOT NULL AND :new_record.ISVALID = 'N' THEN
                vc_ChangeDescription := 'DELETED';
            ELSE
                vc_ChangeDescription := 'UPDATING';
            END IF;


            UPDATE tLIJobsLogs
            SET    ValidTo = SYSDATE
            ,      ISVALID = 'N'
            WHERE  Job_ID  = :old_record.ID
            AND    JobName = :old_record.JobName
            AND    MinSalary = :old_record.MinSalary
            AND    MaxSalary = :old_record.MaxSalary
            AND    ValidTo IS NULL
            AND    ISVALID = 'Y';

            INSERT INTO tLIJobsLogs(Job_ID
                                   ,JobName
                                   ,MinSalary
                                   ,MaxSalary
                                   ,ValidFrom
                                   ,ValidTo
                                   ,ISVALID
                                   ,ChangeMadeBy
                                   ,DateOfChange
                                   ,ChangeDescription
                                   )
            VALUES (:new_record.ID
                   ,:new_record.JobName
                   ,:new_record.MinSalary
                   ,:new_record.MaxSalary
                   ,DEFAULT
                   ,NULL
                   ,'Y'
                   ,SYS_CONTEXT('USERENV', 'CURRENT_USER')
                   ,DEFAULT
                   ,vc_ChangeDescription);


    END CASE;

    DBMS_LOB.FREETEMPORARY(vc_ChangeDescription);

EXCEPTION
    WHEN packSMPLSQLTypes.ge_noDataFoundInMetaView THEN
        DBMS_OUTPUT.PUT_LINE('Can not fetch data from the metaview USER_TAB_COLUMNS');
        RAISE packSMPLSQLTypes.ge_noDataFoundInMetaView;
END trgDMLtEMJobs_logs;

Z góry dzięki za pomoc :)

Nie, dlatego, że do :OLD, :NEW nie możesz odwoływać się dynamicznie. Jakimś rozwiązaniem może być

a) generowanie kodu triggera dla wybranej tabeli: my_extreme_tools.generate_log_trigger(owner=>'MOJSCHEMAT',table_name=>'MOJA_TABELKA')
b) połączenie BEFORE/AFTER update - w before wrzucasz do kolekcji wiersz przed, w after wrzucasz do kolekcji wiersz po i analizujesz kolekcję pod kątem zmian (takie ręczne obejście na :NEW/:OLD)

Od oracle 11 jest coś takiego jak compound trigger, który może uprościć implementację.

p.s.
DBMS_LOB.FREETEMPORARY(vc_ChangeDescription); - co się stanie z tymczasowym lobem, jeśli na insert poleci wyjątek? ;-)

Probowales z dynamicznym SQLem?
W PL/SQL:

• https://stackoverflow.com/a/61780139
• ew. https://blogs.oracle.com/oraclemagazine/post/how-to-dynamically-change-the-columns-in-a-sql-query

Dzięki za pomoc. Wydaje mi się, że takie coś najłatwiej będzie chyba zrobić za pomocą compound trigger.
Co do pytania:

p.s.
DBMS_LOB.FREETEMPORARY(vc_ChangeDescription); - co się stanie z tymczasowym lobem, jeśli na insert poleci wyjątek? ;-)

Tymczasowy LOB został zdefiniowany z duration jako CALL więc i tak powinien zostać zniszczony. A raczej tak mi się wydaje :)

Rozumiem, że chciałbyś to zrobić w czystym SQLu w warstwie bazy danych, i tak technicznie rzecz biorąc byłoby najlepiej.

Nie wiem czy to jest możliwe do zrobienia w PL/SQL, natomiast najłatwiej byłoby to zrobić w warstwie aplikacji i zapisywać do jakiegoś loga tylko i wyłącznie zmiany - w sytuacji, kiedy masz rekord z dużą ilością danych a zmienia się minimalnie tylko wartość jednej kolumny, to o wiele oszczędniej jest zapisać tylko ta mała zmianę a nie cały stary rekord i nowy.

Minusem oczywiście jest to, że logowane są tylko zmiany będące pod kontrolą warstwy aplikacji.

Edit:

dopiszę jeszcze, że ten minus warstwy aplikacji może być plusem.

Otóż:

• możesz chcieć nie zapisywać wszystkich zmian - np. robionych przez jakiegoś admina, jakiegoś kasowania danych i przenoszenia do archiwum
• możesz chcieć zapisać KTO zrobił zmianę, jaki zalogowany do aplikacji użytkownik

Robiąc to w warstwie aplikacji masz o wiele większą kontrolę.

Ja kiedyś namiętnie korzystałem z triggerów / procedur po stronie serwera, teraz całą logikę mam w aplikacji, a baza to jest po prostu magazyn.