Триггеры

-- При обновлении цены товара записываем старую и новую цену в audit_log
CREATE TRIGGER audit_price_changes
AFTER UPDATE OF price ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log(table_name, row_id, old_value, new_value, changed_at, changed_by)
    VALUES ('products', OLD.id, OLD.price, NEW.price, NOW(), CURRENT_USER);
END;

-- При вставке заказа проверяем, что сумма заказа не превышает лимит клиента
CREATE TRIGGER check_order_limit
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE v_limit DECIMAL;
    SELECT credit_limit INTO v_limit FROM customers WHERE id = NEW.customer_id;
    IF NEW.total_amount > v_limit THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Сумма заказа превышает кредитный лимит';
    END IF;
END;

-- При изменении количества товара в заказе пересчитываем общую сумму заказа
CREATE TRIGGER recalc_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders SET total_amount = (
        SELECT SUM(quantity * price) FROM order_items WHERE order_id = NEW.order_id
    ) WHERE id = COALESCE(NEW.order_id, OLD.order_id);
END;

-- При удалении пользователя автоматически удаляем его сессии
CREATE TRIGGER delete_user_sessions
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    DELETE FROM sessions WHERE user_id = OLD.id;
END;

-- При изменении статуса заказа обновляем статус в смежной системе через внешний вызов
CREATE TRIGGER sync_order_status
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status != NEW.status)
BEGIN
    -- Вызов внешнего API (в поддерживающих СУБД)
    PERFORM call_webhook('order_status_changed', NEW.id, NEW.status);
END;

-- UPDATE, который изменяет 1000 строк
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- FOR EACH ROW: триггер выполнится 1000 раз
-- FOR EACH STATEMENT: триггер выполнится 1 раз

-- Сначала создаем функцию, которую будет вызывать триггер
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

-- Затем создаем триггер, привязанный к таблице
CREATE TRIGGER trigger_update_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Триггер для аудита
CREATE OR REPLACE FUNCTION log_order_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_audit(order_id, action, old_data, new_data, changed_at)
        VALUES (NEW.id, 'INSERT', NULL, row_to_json(NEW), NOW());
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_audit(order_id, action, old_data, new_data, changed_at)
        VALUES (NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), NOW());
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO order_audit(order_id, action, old_data, new_data, changed_at)
        VALUES (OLD.id, 'DELETE', row_to_json(OLD), NULL, NOW());
    END IF;
    RETURN NULL;  -- Для AFTER-триггеров возвращаемое значение игнорируется
END;
$$;

CREATE TRIGGER trigger_log_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_order_changes();

-- Триггер на обновление (BEFORE в SQL Server реализуется через INSTEAD OF)
CREATE TRIGGER trg_update_updated_at
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE users SET updated_at = GETDATE()
    WHERE id IN (SELECT id FROM inserted);
END;

-- Триггер для аудита
CREATE TRIGGER trg_audit_orders
ON orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Вставка (таблица inserted содержит новые строки)
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO order_audit (order_id, action, new_data, changed_at)
        SELECT id, 'INSERT', CAST(CONCAT('{"id":', id, ',"total":', total_amount, '}') AS VARCHAR(MAX)), GETDATE()
        FROM inserted;
    END
    
    -- Обновление (есть и inserted, и deleted)
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO order_audit (order_id, action, old_data, new_data, changed_at)
        SELECT 
            i.id, 'UPDATE',
            CAST(CONCAT('{"total":', d.total_amount, '}') AS VARCHAR(MAX)),
            CAST(CONCAT('{"total":', i.total_amount, '}') AS VARCHAR(MAX)),
            GETDATE()
        FROM inserted i
        JOIN deleted d ON i.id = d.id
        WHERE i.total_amount != d.total_amount;
    END
    
    -- Удаление (таблица deleted содержит старые строки)
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO order_audit (order_id, action, old_data, changed_at)
        SELECT id, 'DELETE', CAST(CONCAT('{"id":', id, ',"total":', total_amount, '}') AS VARCHAR(MAX)), GETDATE()
        FROM deleted;
    END
END;

-- Триггер на обновление
DELIMITER //

CREATE TRIGGER update_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//

-- Триггер для проверки бизнес-правила
CREATE TRIGGER check_order_limit
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE v_limit DECIMAL(10,2);
    SELECT credit_limit INTO v_limit FROM customers WHERE id = NEW.customer_id;
    IF NEW.total_amount > v_limit THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order amount exceeds credit limit';
    END IF;
END//

DELIMITER ;

-- Триггер уровня строки
CREATE OR REPLACE TRIGGER update_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

-- Триггер уровня оператора (FOR EACH STATEMENT)
CREATE OR REPLACE TRIGGER log_bulk_update
AFTER UPDATE ON products
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM products WHERE price > 1000;
    INSERT INTO admin_log(message, log_time)
    VALUES ('Bulk update completed. Products with price > 1000: ' || v_count, SYSDATE);
END;
/

CREATE OR REPLACE FUNCTION universal_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log(
        table_name,
        operation,
        row_id,
        old_value,
        new_value,
        changed_at,
        changed_by
    ) VALUES (
        TG_TABLE_NAME,
        TG_OP,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END,
        NOW(),
        CURRENT_USER
    );
    RETURN COALESCE(NEW, OLD);
END;
$$;

-- Триггер с явным порядком
CREATE CONSTRAINT TRIGGER trigger_first
AFTER INSERT ON orders
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION first_function();

CREATE TRIGGER trigger_second
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION second_function();
-- Порядок: сначала триггеры с CONSTRAINT (в алфавитном порядке), потом обычные

-- Установка порядка триггеров
EXEC sp_settriggerorder @triggername = 'trg_first', @order = 'First', @stmttype = 'INSERT';
EXEC sp_settriggerorder @triggername = 'trg_last', @order = 'Last', @stmttype = 'INSERT';
-- Остальные триггеры выполняются в произвольном порядке между First и Last

-- Пример рекурсии (опасно!)
CREATE TRIGGER update_parent
AFTER UPDATE ON categories
FOR EACH ROW
BEGIN
    UPDATE categories SET updated_at = NOW() WHERE id = NEW.parent_id;  -- Может вызвать этот же триггер!
END;

-- PostgreSQL: отключение рекурсии на уровне базы данных
ALTER DATABASE mydb SET session_replication_role = 'replica';  -- временно отключает триггеры

-- SQL Server: управление рекурсией
ALTER DATABASE mydb SET RECURSIVE_TRIGGERS ON;  -- или OFF

-- MySQL: управление через переменную
SET GLOBAL innodb_trx_rseg_n_slots_debug = 0;  -- косвенно

-- Триггер на orders обновляет order_totals
-- Триггер на order_totals обновляет customer_balance
-- Триггер на customer_balance обновляет customer_rating
-- ... и так далее

-- PostgreSQL
CREATE VIEW active_orders AS
SELECT o.id, o.order_date, o.total_amount, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status != 'cancelled';

-- Триггер INSTEAD OF для вставки через представление
CREATE OR REPLACE FUNCTION insert_active_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_customer_id INT;
BEGIN
    -- Найти или создать клиента
    SELECT id INTO v_customer_id FROM customers WHERE name = NEW.customer_name;
    IF NOT FOUND THEN
        INSERT INTO customers (name) VALUES (NEW.customer_name) RETURNING id INTO v_customer_id;
    END IF;
    
    -- Вставить заказ
    INSERT INTO orders (id, customer_id, order_date, total_amount, status)
    VALUES (NEW.id, v_customer_id, NEW.order_date, NEW.total_amount, 'new');
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_insert_active_order
INSTEAD OF INSERT ON active_orders
FOR EACH ROW
EXECUTE FUNCTION insert_active_order();

-- Теперь можно вставлять в представление!
INSERT INTO active_orders (id, order_date, total_amount, customer_name)
VALUES (100, NOW(), 500.00, 'Новый клиент');

-- Операция обновления 10 000 строк
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- FOR EACH ROW: триггер выполнится 10 000 раз
-- FOR EACH STATEMENT: триггер выполнится 1 раз

-- Миграция: вставка 1 000 000 строк
INSERT INTO orders (customer_id, total_amount, created_at)
SELECT customer_id, total_amount, NOW() FROM old_orders;

-- Если есть триггер на аудит, он вставит 1 000 000 записей в audit_log
-- Время выполнения может вырасти с 1 минуты до 1 часа

-- PostgreSQL
ALTER TABLE orders DISABLE TRIGGER ALL;
-- выполнить массовую операцию
ALTER TABLE orders ENABLE TRIGGER ALL;

-- SQL Server
DISABLE TRIGGER trg_audit_orders ON orders;
-- выполнить массовую операцию
ENABLE TRIGGER trg_audit_orders ON orders;

-- MySQL
SET @OLD_TRIGGER_CHECKS = @@SESSION.TRIGGER_CHECKS;
SET SESSION TRIGGER_CHECKS = 0;
-- выполнить массовую операцию
SET SESSION TRIGGER_CHECKS = @OLD_TRIGGER_CHECKS;

CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
WHEN (current_setting('myapp.migration_mode') != 'true')  -- PostgreSQL
BEGIN
    -- логика аудита, которая не выполняется в режиме миграции
END;

-- PostgreSQL
RAISE NOTICE 'Trigger fired: %, operation: %, row_id: %', TG_NAME, TG_OP, NEW.id;

-- SQL Server
PRINT 'Trigger fired: ' + CAST(ROWCOUNT_BIG() AS VARCHAR);

-- MySQL
DECLARE v_message VARCHAR(255) DEFAULT CONCAT('Trigger fired, affected rows: ', ROW_COUNT());
SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = v_message;  -- предупреждение вместо ошибки

CREATE TABLE trigger_debug (
    id SERIAL PRIMARY KEY,
    trigger_name VARCHAR(100),
    event_time TIMESTAMP DEFAULT NOW(),
    message TEXT,
    data JSONB
);

-- В триггере
INSERT INTO trigger_debug (trigger_name, message, data)
VALUES (TG_NAME, 'Order updated', row_to_json(NEW));

-- PostgreSQL
SELECT 
    tgname AS trigger_name,
    tgrelid::regclass AS table_name,
    tgtype,
    tgfoid::regproc AS function_name
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal;

-- SQL Server
SELECT 
    name AS trigger_name,
    OBJECT_NAME(parent_id) AS table_name,
    is_disabled
FROM sys.triggers
WHERE parent_class = 1;  -- 1 = таблица

-- Плохо: триггер делает слишком много всего
CREATE TRIGGER order_processing
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- Проверка кредитного лимита
    -- Обновление остатков на складе
    -- Отправка email клиенту (через внешний вызов)
    -- Запись в аудит
    -- Обновление агрегатов
    -- Вызов вебхука
END;

-- Триггер, который "тихо" изменяет данные
CREATE TRIGGER auto_correct_status
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' AND NEW.paid = false THEN
        SET NEW.status = 'pending';  -- Тихо меняем статус обратно
    END IF;
END;

-- Плохо: обновление той же таблицы в триггере (кроме BEFORE UPDATE)
CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = NOW() WHERE id = NEW.id;  -- Рекурсия!
END;

-- Хорошо
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END;