loggin_trigger-audit, PostgreSQL

loggin_trigger-audit

/*
    trigger na logy AUDIT TRIGGER

CREATE TABLE salaries (
  emp_name TEXT PRIMARY KEY,
  salary   INTEGER NOT NULL
);

CREATE TABLE salary_change_log (
  changed_by TEXT      DEFAULT CURRENT_USER,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  salary_op  TEXT,
  emp_name   TEXT,
  old_salary INTEGER,
  new_salary INTEGER
);

CREATE OR REPLACE FUNCTION log_salary_change()
  RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT'
  THEN
    INSERT INTO salary_change_log (salary_op, emp_name, new_salary)
    VALUES (TG_OP, NEW.emp_name, NEW.salary);
  ELSIF TG_OP = 'UPDATE'
    THEN
      INSERT INTO salary_change_log (salary_op, emp_name, old_salary, new_salary)
      VALUES (TG_OP, NEW.emp_name, OLD.salary, NEW.salary);
  ELSIF TG_OP = 'DELETE'
    THEN
      INSERT INTO salary_change_log (salary_op, emp_name, old_salary)
      VALUES (TG_OP, NEW.emp_name, OLD.salary);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER audit_salary_change
AFTER INSERT OR UPDATE OR DELETE ON salaries
FOR EACH ROW EXECUTE PROCEDURE log_salary_change();

INSERT INTO salaries VALUES ('Bob', 1000);

UPDATE salaries
SET salary = 1100
WHERE emp_name = 'Bob';

INSERT INTO salaries VALUES ('Mary', 1000);

UPDATE salaries
SET salary = salary + 200;

SELECT *
FROM salaries;

SELECT *
FROM salary_change_log;


run \| edit \| history \| help	0