Audit změn v databázi – Oracle

Možná jste někdy narazili na nutnost zaznamenávat změny, které jsou provedeny v databázi. Existuje několik možností – od logování změn na úrovni aplikace (např. jako listener pro Hibernate) až po použití funkce audit trail v Oracle.

My jsem se pro náš dřívejší projekt rozhodli použít trigger, který úpravy zaznamenává.

Protože však všichni uživatelé přistupují do databáze pod stejným uživatelem, musíme triggeru předávat jméno uživatele. K&nsbp;tomuto účelu jsme použili tzv. package, který nastavujeme pomocí uložené funkce:

CREATE OR REPLACE PACKAGE AuditPackage AS
        UserID NUMBER(9, 0);
END;
/
CREATE OR REPLACE FUNCTION SetUserID(newValue NUMBER) RETURN NUMBER AS
prevID NUMBER(9, 0);
BEGIN
        prevID := AuditPackage.UserID;
        AuditPackage.UserID := newValue;
        return prevID;
END;
/

A trigger samotný je nutné aktualizovat pro každou tabulku:

CREATE OR REPLACE TRIGGER ACCOUNTS$AUDITLOG
    AFTER INSERT OR DELETE OR UPDATE ON ACCOUNTS FOR EACH ROW
DECLARE
    action CHAR(1) := NULL;
    curTime TIMESTAMP;
    id NUMBER(9, 0);
    tmpNew VARCHAR2(255);
    tmpOld VARCHAR2(255);
BEGIN
    curTime := current_timestamp;
    SELECT GEN_ID_AUDIT.NextVal INTO id FROM dual;

IF INSERTING THEN
    action := 'I';

    -- Action row
    INSERT INTO AUDITTABLE VALUES (id, curTime, AuditPackage.UserID, action, 'NAZEV_TABULKY', :NEW.ID);

    -- Values
    INSERT INTO AUDITVALUES VALUES (id, 'ISSUERID', :NEW.ISSUERID, :OLD.ISSUERID);
    INSERT INTO AUDITVALUES VALUES (id, 'POOLID', :NEW.POOLID, :OLD.POOLID);

ELSIF UPDATING THEN
    action := 'U';

    -- Action row
    INSERT INTO AUDITTABLE VALUES (id, curTime, AuditPackage.UserID, action, 'NAZEV_TABULKY', :OLD.ID);

    -- Values
    IF UPDATING ('ISSUERID') THEN
        INSERT INTO AUDITVALUES VALUES (id, 'ISSUERID', :NEW.ISSUERID, :OLD.ISSUERID);
    ELSIF UPDATING ('POOLID') THEN
        INSERT INTO AUDITVALUES VALUES (id, 'POOLID', :NEW.POOLID, :OLD.POOLID);

    END IF;
ELSE
    action := 'D';

    -- Action row
    INSERT INTO AUDITTABLE VALUES (id, curTime, AuditPackage.UserID, action, 'NAZEV_TABULKY', :OLD.ID);

    -- Values
    INSERT INTO AUDITVALUES VALUES (id, 'ISSUERID', :NEW.ISSUERID, :OLD.ISSUERID);
    INSERT INTO AUDITVALUES VALUES (id, 'POOLID', :NEW.POOLID, :OLD.POOLID);
END IF;
END;
/

Poznámka: Je nutné nahradit NAZEV_TABULKY za skutečný název tabulky a položky ISSUERID a POOLID jsou názvy dvou položek.

Doufám, že Vám tento trigger pomůže při řešení vašeho problému 🙂