Database Reference
In-Depth Information
) RETURNS VOID AS $$
CONNECT 'dbname=auditdb';
$$ LANGUAGE plproxy;
F
Create trigger functions that use the proxy function to save the data to an external
database:
CREATE OR REPLACE FUNCTION do_emp_audit() RETURNS TRIGGER AS
$$
BEGIN
IF (TG_OP = 'DELETE') THEN
PERFORM log_emp_audit('DEL', user, OLD.empname, OLD.
salary);
ELSIF (TG_OP = 'UPDATE') THEN
-- save old and new values
PERFORM log_emp_audit('OLD', user, OLD.empname, OLD.
salary);
PERFORM log_emp_audit('NEW', user, NEW.empname, NEW.
salary);
ELSIF (TG_OP = 'INSERT') THEN
PERFORM log_emp_audit('INS', user, NEW.empname, NEW.
salary);
END IF;
RETURN NULL; -- result is ignored since this is an AFTER
trigger
END;
$$ LANGUAGE plpgsql;
F
Add the triggers to the emp table:
CREATE TRIGGER emp_remote_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE do_emp_audit();
Ensure that the audit database is secure. This includes checking that the only thing the
audit_logger
user can do is call the
log_emp_audit()
function.
More info on pl/proxy can be found at the following website:
http://plproxy.projects.postgresql.org/doc/tutorial.html
