Tranzakciók adatbázis history képzés kialakitása oracle adatbázis triggerrel

 Tranzakciós adatbázis history kezelése Oracle triggerrel

Az Oracle trigger egy hatékony megoldás lehet history (történeti) adatok kezelésére tranzakciós adatbázisokban. A trigger automatikusan rögzíti a változásokat egy history táblában, így megőrizhetjük az adatok előző állapotait, és biztosíthatjuk az auditálást.

---

Mikor és miért használjunk triggert history kezeléshez?

✅ Automatikus változásnaplózás → Nem kell az alkalmazásban külön kezelni a history táblát.
✅ Adatmódosítások visszakövethetősége → Pl. ki, mikor, milyen értéket módosított.
✅ Auditálási és megfelelőségi követelmények teljesítése → GDPR, SOX, stb.
✅ Minimális alkalmazáskód módosítás → A változások követése az adatbázis szintjén történik.

---

History tábla felépítése

A history tábla tartalmazza az eredeti rekordot és néhány extra mezőt a változások nyomon követésére.

📌 Példa egy ügyféltábla és a hozzá tartozó history tábla létrehozására:

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    address VARCHAR2(200),
    email VARCHAR2(100),
    phone VARCHAR2(50)
);

CREATE TABLE customer_history (
    history_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id NUMBER,
    name VARCHAR2(100),
    address VARCHAR2(200),
    email VARCHAR2(100),
    phone VARCHAR2(50),
    change_type VARCHAR2(10), -- 'INSERT', 'UPDATE', 'DELETE'
    changed_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    changed_by VARCHAR2(100) DEFAULT USER
);


Trigger létrehozása history mentéshez
A következő trigger INSERT, UPDATE és DELETE műveleteket figyel a customers táblán.
📌 Trigger kód:
CREATE OR REPLACE TRIGGER trg_customer_history
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO customer_history (customer_id, name, address, email, phone, change_type)
        VALUES (:NEW.customer_id, :NEW.name, :NEW.address, :NEW.email, :NEW.phone, 'INSERT');

    ELSIF UPDATING THEN
        INSERT INTO customer_history (customer_id, name, address, email, phone, change_type)
        VALUES (:OLD.customer_id, :OLD.name, :OLD.address, :OLD.email, :OLD.phone, 'UPDATE');

    ELSIF DELETING THEN
        INSERT INTO customer_history (customer_id, name, address, email, phone, change_type)
        VALUES (:OLD.customer_id, :OLD.name, :OLD.address, :OLD.email, :OLD.phone, 'DELETE');
    END IF;
END;
/



Mire kell figyelni a triggeres history kezelésnél?
🔹 1. Teljesítményproblémák elkerülése
Trigger befolyásolhatja az írási műveletek sebességét, mivel minden módosításnál extra beszúrás történik a history táblába.
Indexeket kell megfelelően kialakítani a history táblán (customer_id, changed_at).
📌 Megoldás: Ha nagy terhelésű rendszerben dolgozunk, inkább Oracle Flashback vagy CDC (Change Data Capture) megoldásokat használjunk.
🔹 2. Rekurzív triggerek kiküszöbölése
Ha egy trigger olyan műveletet hajt végre, amely újra meghívja ugyanazt a triggert, végtelen ciklus alakulhat ki.
📌 Megoldás: Az autonomous transaction használata vagy WHEN feltételek beállítása a triggerben.
PRAGMA AUTONOMOUS_TRANSACTION;


 Túl nagy history tábla kezelése
Ha sok adat halmozódik fel, a history tábla túl nagy lehet, ami lassíthatja a lekérdezéseket.
Az adatok archiválása időnként szükséges.
📌 Megoldás:
Időszakosan archiváljuk az adatokat külön archív tábla vagy particionálás segítségével.
Példa 90 napnál régebbi adatok törlésére:

DELETE FROM customer_history WHERE changed_at < SYSDATE - 90;


Felhasználói információ mentése
Fontos lehet ki végezte a módosítást (USER vagy SESSION_USER használata).
Példa felhasználónév mentésére:
ALTER TABLE customer_history ADD changed_by VARCHAR2(100);


INSERT INTO customer_history (customer_id, name, address, email, phone, change_type, changed_by)
VALUES (:OLD.customer_id, :OLD.name, :OLD.address, :OLD.email, :OLD.phone, 'UPDATE', USER);


Lekérdezések a history táblára
🔹 Összes változás megtekintése egy ügyfélnél:
SELECT * FROM customer_history WHERE customer_id = 101 ORDER BY changed_at DESC;



🔹 Mikor és hányszor módosították egy ügyfél címét?
SELECT customer_id, address, changed_at FROM customer_history
WHERE customer_id = 101 AND change_type = 'UPDATE'
ORDER BY changed_at DESC;


🔹 Utolsó ismert érték lekérdezése egy adott időpontra:
SELECT * FROM customer_history
WHERE customer_id = 101 AND changed_at = (
    SELECT MAX(changed_at) FROM customer_history WHERE customer_id = 101 AND changed_at <= TO_DATE('2024-01-01', 'YYYY-MM-DD')
);


 Alternatívák a trigger helyett
✔ Oracle Flashback Table              – Ha csak rövid távú visszaállításra van szükség.
✔ Oracle Change Data Capture (CDC)    – Jobb teljesítmény nagy terhelés esetén.
✔ Audit táblák helyett Event Sourcing – Az alkalmazás szintjén történő adatváltozás kezelés.