Blog POSTGRESQL DML LOG MEKANİZMASI

POSTGRESQL DML LOG MEKANİZMASI

Merhaba Arkadaşlar, Postgresql veri tabanınızda DML işlemlerinizi loglamak isterseniz aşağıdaki işlemleri yapabilirsiniz.

Önce tablolarımızı oluşturuyoruz.

CREATE TABLE dba.dml_event (

                SchemaName text NULL,

                TableName text NULL,

                UserName text NULL,

                DMLAction text NULL,

                OriginalData text null,

                ExecutedNewData text NULL,

                ExecutedSQL text null,

                client_id text NULL

);

Trigger kullanılabilsin diye fonksiyon create edilmeli,

CREATE OR REPLACE FUNCTION dba.trg_auditdml()

 RETURNS trigger

 LANGUAGE plpgsql

AS $function$

DECLARE

    OldData TEXT;

    NewData TEXT;

BEGIN

    IF (TG_OP = 'UPDATE') THEN

        OldData := ROW(OLD.*);

        NewData := ROW(NEW.*);

       INSERT INTO dba.dml_event

                (

                               SchemaName

                               ,TableName

                               ,UserName

                               ,DMLAction

                               ,OriginalData

                               ,ExecutedNewData

                               ,ExecutedSQL

                               ,client_id

                )

        VALUES

                (

                               TG_TABLE_SCHEMA::TEXT

                               ,TG_TABLE_NAME::TEXT

                               ,session_user::TEXT

                               ,substring(TG_OP,1,1)

                               ,OldData

                               ,NewData

                               ,current_query(),inet_client_addr());

        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN

        OldData := ROW(OLD.*);

        INSERT INTO dba.dml_event

                (

                               SchemaName

                               ,TableName

                               ,UserName

                               ,DMLAction

                               ,OriginalData

                               ,ExecutedSQL

                               ,client_id

                )

        VALUES

                (

                               TG_TABLE_SCHEMA::TEXT

                               ,TG_TABLE_NAME::TEXT

                               ,session_user::TEXT

                               ,substring(TG_OP,1,1)

                               ,OldData

                               ,current_query(),inet_client_addr()

                );

        RETURN OLD;

    ELSIF (TG_OP = 'INSERT') THEN

        NewData := ROW(NEW.*);

        INSERT INTO dba.dml_event

                (

                               SchemaName

                               ,TableName

                               ,UserName

                               ,DMLAction

                               ,ExecutedNewData

                               ,ExecutedSQL

                               ,client_id

                )

        VALUES

                (

                               TG_TABLE_SCHEMA::TEXT

                               ,TG_TABLE_NAME::TEXT

                               ,session_user::TEXT

                               ,substring(TG_OP,1,1)

                               ,NewData

                               ,current_query(),inet_client_addr()

                );

        RETURN NEW;

    ELSE

        RAISE WARNING '[public.trg_AuditDML] - Other action occurred: %, at %',TG_OP,now();

        RETURN NULL;

    END IF;

END

$function$

;

Son olarak, tabloyu create ederek insert yapıp loglamanın başarılı olduğunu görebilirsiniz.

create table aaa (id integer);

insert into aaa values (1);

select * from dba.dml_event

 

Yazar: Engin YILMAZ