Database Reference
In-Depth Information
-- save old and new values
INSERT INTO emp_audit SELECT 'OLD', now(), user,
OLD.*;
INSERT INTO emp_audit SELECT 'NEW', now(), user,
NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'INS', now(), user,
NEW.*;
ELSEIF (TG_OP = 'TRUNCATE') THEN
INSERT INTO emp_audit SELECT 'TRUNCATE', now(), user,
'-', -1;
END IF;
RETURN NULL; -- result is ignored bacause this is an AFTER
trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
CREATE TRIGGER emp_audit_truncate
AFTER TRUNCATE ON emp
FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();
Collecting changes using triggers and saving them to another
database using dblink or plproxy
For security-critical systems, having the audit logs on the same machine with the rest of the
data may not be enough. In that case, you may need to implement remote-change logging
functionality. One way to do it is using pl/proxy to send change logs to a remote database.
The following is a sample how to log to the preceding example to a remote database auditdb:
F
Create the
emp_audit
log table in the remote auditing database
F
Create a function
log_emp_audit()
in the remote database as follows:
CREATE FUNCTION log_emp_audit(
operation text, userid text, empname text, salary integer
) RETURNS VOID AS
$$
INSERT INTO emp_audit VALUES($1, now(), $2, $3, $4)
$$ LANGUAGE SQL;
F
Create a proxy function for
log_emp_audit()
in the local audited database
(You need to have the pl/proxy language installed in the database for this)
CREATE OR REPLACE FUNCTION log_emp_audit(
operation text, userid text, empname text, salary integer
