loggin_trigger-audit , PostgreSQL

loggin_trigger-audit

/*
    trigger na logy AUDIT TRIGGER

CREATE TABLE vyplaty (
  meno_zamestnanca TEXT PRIMARY KEY,
  plat   INTEGER NOT NULL
);

CREATE TABLE vyplaty_zmeny_log (
  pozmenene_uzivatelom TEXT      DEFAULT CURRENT_USER,
  datum_zmeny TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  druh_operacie  TEXT,
  meno_zamestnanca   TEXT,
  stary_plat INTEGER,
  novy_plat INTEGER
);
REVOKE ALL ON vyplaty_zmeny_log FROM PUBLIC;
/*
Zakomentovane pretoze live databaza nepodporuje vytvaranie uzivatelov
GRANT ALL ON vyplaty_zmeny_log TO managers;
*/

CREATE OR REPLACE FUNCTION log_vyplaty_zmeny()
  RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT'
  THEN
    INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, novy_plat)
    VALUES (TG_OP, NEW.meno_zamestnanca, NEW.plat);
  ELSIF TG_OP = 'UPDATE'
    THEN
      INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, stary_plat, novy_plat)
      VALUES (TG_OP, NEW.meno_zamestnanca, OLD.plat, NEW.plat);
  ELSIF TG_OP = 'DELETE'
    THEN
      INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, stary_plat)
      VALUES (TG_OP, NEW.meno_zamestnanca, OLD.plat);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER audit_vyplaty_zmena
AFTER INSERT OR UPDATE OR DELETE ON vyplaty
FOR EACH ROW EXECUTE PROCEDURE log_vyplaty_zmeny();

INSERT INTO vyplaty VALUES ('Rob', 1000);

UPDATE vyplaty
SET plat = 1100
WHERE meno_zamestnanca = 'Rob';

INSERT INTO vyplaty VALUES ('Maria', 1000);

UPDATE vyplaty
SET plat = plat + 200;

SELECT *
FROM vyplaty;

SELECT *
FROM vyplaty_zmeny_log;


run \| edit \| history \| help	0