EVENT TRIGGERS

Ushbu qo'llanmada PostgreSQLda ma'lumotlar bazasi tuzilmasidagi o'zgarishlarni (DDL buyruqlarini) kuzatish, audit qilish yoki taqiqlash uchun hodisa trigirlari (Event Triggers) dan qanday foydalanishni o'rganasiz.

Hodisa trigirlari (Event Triggers) nima?

Oddiy trigirlar DML buyruqlariga (INSERT, UPDATE, DELETE) va aniq bitta jadvalga biriktiriladi.

Hodisa trigirlari (Event Triggers) esa butun ma'lumotlar bazasi miqyosida ishlaydi va DDL (Data Definition Language) buyruqlari (masalan, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX) bajarilganda ishga tushadi.

Hodisa trigirlarining qo'llanilish sohalari:

• Ma'lumotlar bazasi sxemasining barcha o'zgarishlarini audit qilish va loglash.
• Ruxsat etilmagan foydalanuvchilar tomonidan jadvallarni o'chirish yoki o'zgartirishni taqiqlash.
• Yangi yaratilgan jadvallarga avtomatik tarzda xavfsizlik qoidalarini (grantlar, policy) o'rnatish.

Qo'llab-quvvatlanadigan DDL hodisalari

PostgreSQL quyidagi asosiy hodisalarni taqdim etadi:

• ddl_command_start — DDL buyrug'i bajarilishidan oldin ishlaydi.
• ddl_command_end — DDL buyrug'i bajarilib bo'lgandan keyin ishlaydi.
• table_rewrite — jadval to'liq qayta yozilishiga sabab bo'luvchi DDL amallarida ishlaydi.
• sql_drop — har qanday ma'lumotlar bazasi ob'ekti o'chirilishidan oldin ishlaydi.

CREATE EVENT TRIGGER sintaksisi

CREATE EVENT TRIGGER trigger_name
ON event_name
[ WHEN condition ]
EXECUTE FUNCTION function_name();

[!NOTE] Hodisa trigiri chaqiradigan funksiya RETURNS EVENT_TRIGGER turiga ega bo'lishi shart. Ushbu funksiya ichida DDL amali haqida to'liq ma'lumot olish uchun pg_event_trigger_ddl_commands() tizim funksiyasidan foydalaniladi.

Amaliy misol: Sxema o'zgarishlarini audit qilish

Keling, bazada kim qanday DDL o'zgarishi qilganini qayd etib boruvchi hodisa trigiri tuzamiz:

1) Log jadvalini yaratish

DROP TABLE IF EXISTS event_logs CASCADE;

CREATE TABLE event_logs (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type VARCHAR NOT NULL,
    command_tag VARCHAR NOT NULL,
    object_id OID NOT NULL,
    object_name VARCHAR NOT NULL,
    schema_name VARCHAR NOT NULL,
    user_name VARCHAR NOT NULL,
    event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2) Hodisa trigiri funksiyasini tuzish

CREATE OR REPLACE FUNCTION log_events()
RETURNS EVENT_TRIGGER
AS 
$$
BEGIN
    INSERT INTO event_logs (
        event_type,
        command_tag,
        object_id,
        object_name,
        schema_name,
        user_name
    )
    SELECT
        tg_event,
        tg_tag,
        objid,
        object_identity,
        schema_name,
        current_user
    FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;

3) Hodisa trigirini yaratish

CREATE EVENT TRIGGER log_schema_changes
ON ddl_command_end
EXECUTE FUNCTION log_events();

4) DDL buyrug'ini bajarib sinash

Jadvalga yangi ustun qo'shamiz:

ALTER TABLE products ADD COLUMN net_weight DEC;

Endi event_logs jadvalini tekshiramiz:

SELECT event_type, command_tag, object_name, user_name, event_time
FROM event_logs;

Natija:

   event_type    | command_tag |   object_name   | user_name |          event_time           
-----------------+-------------+-----------------+-----------+-------------------------------
 ddl_command_end | ALTER TABLE | public.products | postgres  | 2026-09-22 01:05:30.123456+05

PostgreSQL har qanday ALTER TABLE, CREATE TABLE yoki boshqa DDL buyrug'ini ushlab, uni muallifi va ob'ekti bilan birga loglab qo'yadi!

5) DDL amallarini taqiqlash misoli

Aytaylik, oddiy foydalanuvchilar DDL buyruqlarini bajara olmasligini ta'minlamoqchimiz:

CREATE OR REPLACE FUNCTION stop_ddl() 
RETURNS EVENT_TRIGGER 
AS 
$$
BEGIN
   IF current_user <> 'postgres' THEN
      RAISE EXCEPTION 'Foydalanuvchi (%) ma''lumotlar bazasi tuzilmasini o''zgartirish huquqiga ega emas!', current_user;
   END IF;
END;
$$ 
LANGUAGE plpgsql;

CREATE EVENT TRIGGER stop_executing_ddl 
ON ddl_command_start
EXECUTE FUNCTION stop_ddl();

Hodisa trigirlarini boshqarish

Bazada mavjud hodisa trigirlarini ko'rish:

SELECT evtname, evtevent, evtenabled FROM pg_event_trigger;

Hodisa trigirini to'xtatish (disable):

ALTER EVENT TRIGGER log_schema_changes DISABLE;

Hodisa trigirini qayta yoqish (enable):

ALTER EVENT TRIGGER log_schema_changes ENABLE;

Hodisa trigirini o'chirish:

DROP EVENT TRIGGER log_schema_changes;
DROP EVENT TRIGGER stop_executing_ddl;

Xulosa

• Event Triggers alohida jadvalga emas, butun ma'lumotlar bazasiga taalluqli bo'lib, DDL amallarini boshqaradi.
• Ular bazadagi tuzilmaviy o'zgarishlarni audit qilish, ruxsatsiz o'zgartirishlarni to'xtatish uchun kuchli himoya vositasi hisoblanadi.
• pg_event_trigger_ddl_commands() orqali bajarilgan DDL ob'ektlari haqida to'liq ma'lumot olish mumkin.