Тригери

Тригери се објекти како придружни табели, кои автоматски се активираат кога ке се случи нешто во врска со таа табела за која се врзани тригерите. Креирање на тригери се дефинира со CREATE TRIGGER.
Тригери се активираат при извршување на наредбите INSERT,UPDATE и DELETE.
Време на извршување на тригери е BEFORE и AFTER.
Според самата функција може да видиме кога се извршува тригерот BEFORE,
што значи овој тригер ке се активира пред секој нов запис во табела.
Во тригерот INSERT, може да се користи само NEW.Ime_Na_Pole.
Во тригерот UPDATE, може да се користи само OLD.Ime_Na_Pole.
При манипулација со комплексни тригери користиме функции како BEGIN и END,
додека при бришење користиме DROP и при прикажување користиме SHOW.
Исто така, да напоменеме дека тие може да се активираат
по редови FOR EACH ROW и по операции FOR EACH STATEMENT.

Тригер за автоматско пресметување на total_price при внес во contains или при update

Овој тригер автоматски ја ажурира total_price во orders секој пат кога ќе се додаде,
смени или избрише некој елемент во contains.
Целта е да се осигураме дека orders.total_price е точен збир од сите quantity * price вредности
во contains за таа нарачка.
Првин се креира функција :

CREATE OR REPLACE FUNCTION update_order_total_price()
RETURNS TRIGGER AS $$
DECLARE
  order_id INTEGER;
BEGIN
  IF TG_OP = 'DELETE' THEN
    order_id := OLD.id_order;
  ELSE
    order_id := NEW.id_order;
  END IF;

  UPDATE orders
  SET total_price = (
    SELECT COALESCE(SUM(quantity * price), 0)
    FROM contains
    WHERE id_order = order_id
  )
  WHERE id_order = order_id;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Потоа се креира тригерот што ја повикува таа функција:

CREATE TRIGGER trg_update_total_price
AFTER INSERT OR UPDATE OR DELETE ON contains
FOR EACH ROW
EXECUTE FUNCTION update_order_total_price();

Тригер за намалување на залиха при додавање во contains (нарачка)

Овој тригер автоматски служи да намали на stock.quantity кога се направи нарачка.
Првин се креира функција :

CREATE OR REPLACE FUNCTION decrement_stock_quantity()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE stock
  SET quantity = quantity - NEW.quantity,
      updated_at = NOW()
  WHERE id_stock = NEW.id_stock;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Потоа се креира тригерот што ја повикува таа функција:

CREATE TRIGGER trg_decrement_stock
AFTER INSERT ON contains
FOR EACH ROW
EXECUTE FUNCTION decrement_stock_quantity();

Тригер за враќање на залиха ако се избрише елемент од contains

Овој тригер служи за враќање на количина во stock ако се избрише елемент од нарачка (откажана, вратена итн.)
односно, ако се избрише ред од contains,пример за тоа е вратен производ, количината да се врати назад.
Првин се креира функција :

CREATE OR REPLACE FUNCTION increment_stock_on_delete()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE stock
  SET quantity = quantity + OLD.quantity,
      updated_at = NOW()
  WHERE id_stock = OLD.id_stock;

  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Потоа се креира тригерот што ја повикува таа функција:

CREATE TRIGGER trg_increment_stock
AFTER DELETE ON contains
FOR EACH ROW
EXECUTE FUNCTION increment_stock_on_delete();

Тригер за враќање на залиха ако нарачка не се испорача

Првин се креира функција :

CREATE OR REPLACE FUNCTION restock_if_cancelled()
RETURNS TRIGGER AS $$
DECLARE
  row RECORD;
BEGIN
  IF TG_OP = 'UPDATE' AND OLD.order_status <> 'cancelled' AND NEW.order_status = 'cancelled' THEN
    FOR row IN 
      SELECT c.id_stock, c.quantity 
      FROM contains c 
      WHERE c.id_order = NEW.id_order
    LOOP
      UPDATE stock
      SET quantity = quantity + row.quantity,
          updated_at = NOW()
      WHERE id_stock = row.id_stock;
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Потоа се креира тригерот што ја повикува таа функција:

CREATE TRIGGER trg_restock_on_cancel
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION restock_if_cancelled();

Сумарно

Како знаеме дека она што сме го направиме се навистина тригери?
-> Тригерите се врзани за табелите contains, orders, stock
и се активираат автоматски на INSERT,UPDATE,DELETE
-> Сите тригери како trg_update_total_price, trg_decrement_stock... се дефинирани преку CREATE TRIGGER
-> Извршување на наредби како INSERT,UPDATE,DELETE
trg_update_total_price се активира на INSERT,UPDATE и DELETE на contains
trg_decrement_stock на INSERT
trg_increment_stock на DELETE
-> Употреба на BEFORE и AFTER
Сите тригери се AFTER, што е логично бидејќи сакаме да настапи откако ќе се случи акцијата
-> Употреба на NEW и OLD
Конкретен пример е update_order_total_price()

IF TG_OP = 'DELETE' THEN
  order_id := OLD.id_order;
ELSE
  order_id := NEW.id_order;
END IF;

-> Употреба на BEGIN и END

BEGIN
  -- логика
END;

->Сите тригери работат на ниво на ред FOR EACH ROW

Проверка

Како ке провериме дали се успешно додадени тригерите?
Со помош на следнава линија ке провериме директно во базата

SELECT * FROM pg_trigger WHERE tgname LIKE 'trg_%';

Тригер за автоматско ажурирање на колона updated_at

Со помош на овој тригер секој пат кога ќе се смени нешто во ред од табелата contains,
PostgreSQL автоматски ја ажурира колоната updated_at со тековниот датум и време.
Со тоа овозможуваме да имаме увид кога последен пат била изменета некоја количина на производ.
За да не го правиме тоа рачно секој пат,првин креираме тригер :

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = CURRENT_TIMESTAMP;
   RETURN NEW;
END;
$$ language 'plpgsql';

Потоа се креира тригерот што ја повикува таа функција:

CREATE TRIGGER update_contains_updated_at
BEFORE UPDATE ON contains
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

За да направиме да се ажурира updated_at автоматски кога менуваме нешто во табелата, креираме тригер:

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = CURRENT_TIMESTAMP;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Проверка

Како ке знаеме дека овој тригер работи?