1/ Creating a table for Trigger Data:
-- SEQUENCE: audit.audit_log_id_seq
-- DROP SEQUENCE IF EXISTS audit.audit_log_id_seq;
CREATE SEQUENCE IF NOT EXISTS audit.audit_log_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
ALTER SEQUENCE audit.audit_log_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE audit.audit_log_id_seq TO postgres;
/
-- Table: audit.audit_log
-- DROP TABLE IF EXISTS audit.audit_log;
CREATE TABLE IF NOT EXISTS audit.audit_log
(
id bigint NOT NULL
DEFAULT nextval('audit.audit_log_id_seq'::regclass),
event_time text COLLATE pg_catalog."default",
client_addr inet,
application_name text COLLATE pg_catalog."default",
username text COLLATE pg_catalog."default",
table_name text COLLATE pg_catalog."default",
operation text COLLATE pg_catalog."default",
before_value json,
after_value json,
CONSTRAINT audit_log_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS audit.audit_log OWNER to postgres;
GRANT ALL ON TABLE audit.audit_log TO postgres;
------------------------------
2/ Creating a Audit Trigger:
-- FUNCTION: audit.audit_trigger()
-- DROP FUNCTION IF EXISTS audit.audit_trigger();
CREATE OR REPLACE FUNCTION audit.audit_trigger()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
old_row json := NULL;
new_row json := NULL;
BEGIN
IF TG_OP IN ('UPDATE','DELETE') THEN
old_row = row_to_json(OLD);
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
new_row = row_to_json(NEW);
END IF;
INSERT INTO audit.audit_log(
client_addr,
application_name,
username,
event_time,
table_name,
operation,
before_value,
after_value
) VALUES (
inet_client_addr(),
current_setting('application_name'),
session_user,
current_timestamp,
TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
TG_OP,
old_row,
new_row
);
RETURN NEW;
END;
$BODY$;
ALTER FUNCTION audit.audit_trigger() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION audit.audit_trigger() TO postgres;
------------------
3/ Insert Trigger to Table "public.salons":-- Trigger: audit_log__public_salons
-- DROP TRIGGER IF EXISTS audit_log__public_salons ON public.salons;
CREATE TRIGGER audit_log__public_salons
AFTER INSERT OR DELETE OR UPDATE
ON public.salons
FOR EACH ROW
EXECUTE PROCEDURE audit.audit_trigger();
------------------
4/ Result:
SELECT * FROM audit.audit_log ORDER BY id ASC LIMIT 100
