97 / 100

DISABLE TRIGGER

Trigirlarni o'chirib qo'yish

Ushbu qo'llanmada PostgreSQLda trigirlarni butunlay o'chirib tashlamasdan, ma'lumotlarni ommaviy yuklash (bulk insert) yoki texnik xizmat ko'rsatish davomida ularni vaqtincha to'xtatib qo'yish (DISABLE TRIGGER) va qayta yoqish (ENABLE TRIGGER) usullarini o'rganasiz.

Trigirni vaqtincha to'xtatish (DISABLE TRIGGER)

Ba'zan katta hajmdagi ma'lumotlarni bazaga import qilish yoki ma'lumotlarni tozalash jarayonida trigirlar ishlashi unumdorlikni pasaytiradi yoki xatoliklarga olib kelishi mumkin. Bunday hollarda trigirni o'chirish shart emas — uni vaqtincha o'chirib qo'yish (disable) kifoya.

Sintaksisi:

query.sql

ALTER TABLE table_name
DISABLE TRIGGER trigger_name | ALL;

Sintaksis tushuntirishi:

trigger_name — aynan bitta trigirni o'chirib qo'yadi.
ALL — ko'rsatilgan jadvaldagi barcha foydalanuvchi trigirlarini vaqtincha o'chiradi.

Trigirni qayta yoqish (ENABLE TRIGGER)

Ishlar tugagach, trigirlarni asl holatiga qaytarish uchun:

query.sql

ALTER TABLE table_name
ENABLE TRIGGER trigger_name | ALL;

Amaliy misol

Avval narxlar o'zgarishini loglaydigan jadval va trigir yaratamiz:

query.sql

DROP TABLE IF EXISTS price_change_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(11, 2) NOT NULL
);

CREATE TABLE price_change_logs (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(11, 2) NOT NULL,
    new_price DECIMAL(11, 2) NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE
);

INSERT INTO products (product_name, price) VALUES
('Samsung Galaxy S24', 999.99),
('Apple iPhone 15', 899.99);

-- Trigir funksiyasi
CREATE OR REPLACE FUNCTION log_price_changes()
RETURNS TRIGGER 
AS 
$$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_change_logs (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigir
CREATE TRIGGER price_update_trigger
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_changes();

1) Oddiy holatda trigirning ishlashi

query.sql

UPDATE products
SET price = 799.99
WHERE product_id = 1;

Log jadvalini tekshiramiz:

query.sql

SELECT product_id, old_price, new_price FROM price_change_logs;

Natija:

matn

 product_id | old_price | new_price 
------------+-----------+-----------
          1 |    999.99 |    799.99

Trigir ishladi va narx o'zgarishini saqladi.

2) Trigirni to'xtatib qo'yish (DISABLE)

Endi ommaviy tuzatish kiritishimiz kerak bo'lib, log yozilishini xohlamaymiz:

query.sql

ALTER TABLE products
DISABLE TRIGGER price_update_trigger;

Endi 2-mahsulot narxini o'zgartiramiz:

query.sql

UPDATE products
SET price = 999.99
WHERE product_id = 2;

Log jadvalini qayta tekshiramiz:

query.sql

SELECT product_id, old_price, new_price FROM price_change_logs WHERE product_id = 2;

Natija:

matn

 product_id | old_price | new_price 
------------+-----------+-----------
(0 rows)

Trigir o'chirilgani sababli logga hech narsa yozilmadi!

3) Barcha trigirlarni to'xtatish

Jadvaldagi barcha trigirlarni bir vaqtda to'xtatish:

query.sql

ALTER TABLE products 
DISABLE TRIGGER ALL;

4) Trigirni qayta yoqish (ENABLE)

Amallar yakunlangach, trigirni qayta faollashtiramiz:

query.sql

ALTER TABLE products
ENABLE TRIGGER price_update_trigger;

Yoki hammasini bir vaqtda:

query.sql

ALTER TABLE products
ENABLE TRIGGER ALL;

Xulosa

DISABLE TRIGGER vaqtinchalik import, eksport yoki migratsiya jarayonlarida trigirni o'chirmasdan turib to'xtatib turish uchun juda qulay.
ENABLE TRIGGER trigir faoliyatini asl holiga qaytaradi.
ALL kalit so'zi jadvaldagi barcha trigirlarga birgalikda ta'sir qiladi.

Oldingi

Keyingi