pg-auditing.sql

-- şema içerisinde audit_logs tablosu 
CREATE TABLE audit_logs (
    id serial PRIMARY KEY,  
    operation VARCHAR(50) NOT NULL,
    table_name VARCHAR(255) NOT NULL,
    old_data TEXT,
    new_data TEXT,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(255)
);

-- delete update ve insertte çalışan bir trigger fonksiyonu oluşturulur 

CREATE OR REPLACE FUNCTION audit_trigger_func() RETURNS TRIGGER AS $body$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_logs(changed_by, operation, table_name, old_data) VALUES (current_user, 'DELETE', TG_TABLE_NAME, OLD::text);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_logs(changed_by, operation, table_name, old_data, new_data) VALUES (current_user, 'UPDATE', TG_TABLE_NAME, OLD::text, NEW::text);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_logs(changed_by, operation, table_name, new_data) VALUES (current_user, 'INSERT', TG_TABLE_NAME, NEW::text);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$body$ LANGUAGE plpgsql;


-- sonraki oluşturulacak tablolar için

CREATE OR REPLACE FUNCTION auto_add_audit_trigger()
RETURNS event_trigger LANGUAGE plpgsql
AS $$
DECLARE 
    obj record;
BEGIN
    FOR obj IN (SELECT * FROM pg_event_trigger_ddl_commands())
    LOOP
        IF obj.schema_name = 'your_schema_name' THEN
            EXECUTE format('CREATE TRIGGER sample_table_audit_trigger 
                            AFTER INSERT OR UPDATE OR DELETE 
                            ON %I.%I 
                            FOR EACH ROW 
                            EXECUTE FUNCTION audit_trigger_func();', obj.schema_name, obj.object_name);
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER trigger_on_create_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION auto_add_audit_trigger();


-- eski tabloların için.

DO $$ 
DECLARE 
    table_name text; 
BEGIN 
    FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'your_schema_name')
    LOOP
        EXECUTE format('CREATE TRIGGER sample_table_audit_trigger 
                        AFTER INSERT OR UPDATE OR DELETE 
                        ON %I 
                        FOR EACH ROW 
                        EXECUTE FUNCTION audit_trigger_func();', table_name);
    END LOOP;
END $$;