100 / 100

AFTER INSERT TRIGGER

Qo'shgandan keyingi trigirlar

Ushbu qo'llanmada PostgreSQLda jadvalga yangi qator muvaffaqiyatli kiritilgandan so'ng bog'liq jadvallarni avtomatik yangilash uchun AFTER INSERT trigirlaridan qanday foydalanishni o'rganasiz.

AFTER INSERT trigirlariga kirish

AFTER INSERT trigiri jadvalga yangi qator qo'shilib, uning birlamchi kaliti (ID) shakllanib, barcha cheklovlar tekshirilib bo'linganidan keyin ishga tushadi.

AFTER INSERT trigirlari odatda quyidagi vazifalarni bajarish uchun ishlatiladi:

Bog'langan jadvallardagi agregat qiymatlar va qoldiqlarni yangilash: Masalan, yangi tranzaksiya kiritilgach, ombordagi tovar sonini oshirish yoki kamaytirish.
Audit va xabarnomalar yuborish: Yangi foydalanuvchi yoki buyurtma qo'shilganda log yozish yoki tashqi tizimlarga signal uzatish.
Kaskadli ma'lumot yaratish: Yangi profil, hisob raqami yoki standart sozlamalarni avtomatik shakllantirish.

Sintaksisi:

query.sql

CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name
FOR EACH { ROW | STATEMENT }
EXECUTE FUNCTION trigger_function();

Amaliy misol: Tranzaksiya kiritilganda ombor qoldig'ini avtomatik yangilash

Keling, transactions jadvaliga tovar qabul qilish (receipt) yoki tovar chiqarish (issue) yozuvi qo'shilganda, inventories jadvalidagi tovar sonini avtomatik hisoblab yangilaydigan trigir yaratamiz:

1) Jadvallarni yaratish va boshlang'ich ma'lumotlar

query.sql

DROP TABLE IF EXISTS transactions CASCADE;
DROP TABLE IF EXISTS inventories CASCADE;

CREATE TABLE inventories (
    inventory_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    UNIQUE (product_id, warehouse_id)
);

CREATE TABLE transactions (
    transaction_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    user_id INT NOT NULL,
    type VARCHAR(20) NOT NULL,
    quantity INT NOT NULL,
    transaction_date DATE NOT NULL
);

-- 1-omborimizda 1-mahsulotdan 100 dona bor
INSERT INTO inventories (product_id, warehouse_id, quantity)
VALUES (1, 1, 100);

2) AFTER INSERT trigir funksiyasini yaratish

query.sql

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER
AS
$$
BEGIN
    -- Qoldiqni yangilashga urinamiz
    UPDATE inventories
    SET quantity = quantity + CASE
        WHEN NEW.type = 'receipt' THEN NEW.quantity
        WHEN NEW.type = 'issue' THEN -NEW.quantity
        ELSE 0
    END
    WHERE product_id = NEW.product_id AND warehouse_id = NEW.warehouse_id;

    -- Agar omborda bu tovar qatori hali mavjud bo'lmasa (NOT FOUND):
    IF NOT FOUND THEN
        IF NEW.type = 'receipt' THEN
            INSERT INTO inventories (product_id, warehouse_id, quantity)
            VALUES (NEW.product_id, NEW.warehouse_id, NEW.quantity);
        ELSE
            RAISE EXCEPTION 
                'Tovarni chiqarib bo''lmaydi: Omborda bunday mahsulot yozuvi mavjud emas (mahsulot: %, ombor: %)', 
                NEW.product_id, NEW.warehouse_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

3) AFTER INSERT trigirini biriktirish

query.sql

CREATE TRIGGER after_transaction_insert
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_inventory();

4) Trigirni sinab ko'rish

Hozirda 1-omborimizdagi 1-mahsulot sonini tekshiramiz:

query.sql

SELECT product_id, warehouse_id, quantity 
FROM inventories 
WHERE product_id = 1 AND warehouse_id = 1;

Natija:

matn

 product_id | warehouse_id | quantity 
------------+--------------+----------
          1 |            1 |      100

Endi omborga yana 20 dona tovar qabul qilindi deb tranzaksiya kiritamiz:

query.sql

INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
VALUES (1, 1, 1, 'receipt', 20, '2026-09-22');

Tranzaksiya kiritilishi bilanoq AFTER INSERT trigiri avtomatik ishga tushib, ombordagi qoldiqni yangiladi. Qayta tekshiramiz:

query.sql

SELECT product_id, warehouse_id, quantity 
FROM inventories 
WHERE product_id = 1 AND warehouse_id = 1;

Natija:

matn

 product_id | warehouse_id | quantity 
------------+--------------+----------
          1 |            1 |      120

Qoldiq avtomatik ravishda 100 dan 120 ga ko'paydi!

Endi 30 dona chiqarish (issue) tranzaksiyasini kiritamiz:

query.sql

INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
VALUES (1, 1, 1, 'issue', 30, '2026-09-22');

Tekshirish:

query.sql

SELECT product_id, warehouse_id, quantity 
FROM inventories 
WHERE product_id = 1 AND warehouse_id = 1;

Natija:

matn

 product_id | warehouse_id | quantity 
------------+--------------+----------
          1 |            1 |       90

Qoldiq avtomatik tarzda 90 ga kamaydi.

Xulosa

AFTER INSERT trigiri asosiy qator muvaffaqiyatli saqlangandan keyin boshqa jadvallardagi o'zgarishlarni va hisob-kitoblarni sinxronlashtirish uchun ishlatiladi.
NEW o'zgaruvchisi orqali qo'shilgan yangi qator ma'lumotlaridan foydalaniladi.
NOT FOUND tekshiruvi orqali bog'liq jadvalda qator bo'lmasa avtomatik yangi yozuv yaratish imkoniyati mavjud.

Oldingi

Tugatish