95 / 100

TRIGGERS

Trigirlar haqida tushuncha

Ushbu qo'llanmada PostgreSQLda ma'lumotlar bazasi vazifalarini avtomatlashtirish, audit yuritish va murakkab biznes qoidalarini tekshirish uchun trigirlar (triggers) dan qanday foydalanishni o'rganasiz.

PostgreSQL trigirlariga kirish

PostgreSQLda trigir (trigger) — bu jadval yoki ko'rinishda (view) muayyan hodisa (masalan, INSERT, UPDATE, DELETE yoki TRUNCATE) yuz berganda avtomatik tarzda ishga tushadigan maxsus funksiya yoki protseduradir.

Amaliyotda trigirlar quyidagi maqsadlarda keng qo'llaniladi:

Audit va log yuritish: ma'lumotlarni kim, qachon va qanday o'zgartirganini qayd etish.
Murakkab cheklovlar: oddiy CHECK yoki FOREIGN KEY cheklovlari bajara olmaydigan murakkab mantiqiy tekshiruvlar.
Avtomatik hisob-kitoblar va sinxronlash: bitta jadvaldagi o'zgarishga qarab boshqa jadvallardagi qoldiqlarni yangilash.

Trigirning asosiy tarkibiy qismlari

Trigir to'rtta asosiy komponentdan iborat:

Trigir hodisasi (Trigger Event): Trigirni uyg'otuvchi amal: INSERT, UPDATE, DELETE, TRUNCATE.
Trigir vaqti (Trigger Timing):
- BEFORE — hodisa bajarilishidan oldin ishlaydi (odatda ma'lumotlarni tekshirish yoki o'zgartirish uchun).
- AFTER — hodisa muvaffaqiyatli bajarilgandan keyin ishlaydi (odatda log yozish yoki boshqa jadvallarni yangilash uchun).
- INSTEAD OF — ko'rinishlarda (views) amalning o'rniga ishlaydi.
Trigir qamrovi (Trigger Scope):
- FOR EACH ROW — o'zgarishga uchragan har bir qator uchun alohida ishga tushadi (masalan, 10 ta qator yangilansa, 10 marta ishlaydi).
- FOR EACH STATEMENT — butun SQL buyrug'i uchun faqat bitta marta ishlaydi.
Trigir funksiyasi (Trigger Function): Hodisa sodir bo'lganda bajariladigan kod bloki. U RETURNS TRIGGER turiga ega bo'lishi shart.

Maxsus trigir o'zgaruvchilari

Trigir funksiyasi ichida quyidagi maxsus o'zgaruvchilar mavjud bo'ladi:

TG_OP — qaysi amal bajarilganini bildiruvchi matn ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE').
NEW — yangi qo'shilayotgan yoki o'zgartirilgan qator ma'lumotlari (INSERT va UPDATE paytida mavjud).
OLD — o'zgartirilishdan yoki o'chirilishdan oldingi eski qator ma'lumotlari (UPDATE va DELETE paytida mavjud).

CREATE TRIGGER sintaksisi

Trigir ikki bosqichda yaratiladi:

RETURNS TRIGGER qaytaruvchi funksiya yaratiladi (odatda PL/pgSQL tilida).
CREATE TRIGGER buyrug'i orqali funksiya jadval hodisasiga bog'lanadi:

query.sql

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | DELETE | TRUNCATE | UPDATE [OF column_name, ...] }
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE FUNCTION function_name(arguments);

Amaliy misol: Mahsulot narxi va zaxirasi o'zgarishini loglash

Keling, products jadvalidagi xavfsizlik zaxirasi (safety_stock) o'zgarganda buni avtomatik audit jadvaliga yozib boruvchi trigir yaratamiz:

Products Jadvali

1) Asosiy jadval va log jadvalini yaratish

query.sql

DROP TABLE IF EXISTS safety_stock_logs CASCADE;
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  safety_stock INT NOT NULL DEFAULT 0
);

CREATE TABLE safety_stock_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  old_safety_stock INT NOT NULL,
  new_safety_stock INT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE
);

INSERT INTO products (product_name, price, safety_stock)
VALUES ('Samsung Galaxy S24', 999.99, 20);

2) Trigir funksiyasini yaratish

query.sql

CREATE OR REPLACE FUNCTION log_safety_stock_changes()
RETURNS TRIGGER 
AS 
$$
BEGIN
  IF NEW.safety_stock <> OLD.safety_stock THEN
    INSERT INTO safety_stock_logs (product_id, old_safety_stock, new_safety_stock)
    VALUES (OLD.product_id, OLD.safety_stock, NEW.safety_stock);
  END IF;
  RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

3) Trigirni jadvalga biriktirish

query.sql

CREATE OR REPLACE TRIGGER safety_stock_update_trigger
AFTER UPDATE OF safety_stock
ON products
FOR EACH ROW
EXECUTE FUNCTION log_safety_stock_changes();

Bu trigir faqat safety_stock ustuni yangilanganda (AFTER UPDATE OF safety_stock), har bir qator uchun (FOR EACH ROW) log_safety_stock_changes() funksiyasini chaqiradi.

4) Trigirni sinab ko'rish

Mahsulot zaxirasini 20 dan 15 ga o'zgartiramiz:

query.sql

UPDATE products 
SET safety_stock = 15
WHERE product_id = 1;

Endi safety_stock_logs audit jadvalini tekshiramiz:

query.sql

SELECT product_id, old_safety_stock, new_safety_stock, updated_at
FROM safety_stock_logs;

Natija:

matn

 product_id | old_safety_stock | new_safety_stock |          updated_at           
------------+------------------+------------------+-------------------------------
          1 |               20 |               15 | 2026-09-22 01:05:00.123456+05

Ko'rib turganingizdek, UPDATE buyrug'i bajarilishi bilan trigir ishga tushib, eski va yangi qiymatlarni sana bilan birga avtomatik saqlab qo'ydi!

Xulosa

Trigirlar jadvalda ma'lumotlar o'zgarganda avtomatik ishga tushadigan qoidalardir.
BEFORE ma'lumotni kiritishdan oldin tekshirish va to'g'rilash uchun, AFTER esa audit va bog'liq amallar uchun ishlatiladi.
NEW yangi qiymatni, OLD esa eski qiymatni ifodalaydi.
Trigir funksiyalari RETURNS TRIGGER ko'rsatilgan holda PL/pgSQL tilida yoziladi.

Oldingi

Keyingi