BEFORE в MySQL — 100 практических приёмов

100 приёмов MySQL BEFORE: нормализация, валидация, SIGNAL, автозаполнение, политика данных.
MySQL

🟢 BEFORE в MySQL. Введение

BEFORE в MySQL — момент срабатывания триггера до фактической записи данных. С его помощью удобно валидировать вход, нормализовать значения, автозаполнять поля и блокировать недопустимые операции через SIGNAL. Ниже — синтаксис, типовой шаблон и 100 уникальных примеров.

Синтаксис

DELIMITER //
CREATE TRIGGER trg_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  -- изменяем NEW.* или валидируем
  SET NEW.col = IFNULL(NEW.col, 'value');
END;//
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_name_upd
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
  -- защита и проверки
  IF NEW.status='closed' AND OLD.status<>'closed' THEN
    SET NEW.closed_at = NOW();
  END IF;
END;//
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_name_del
BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
  -- запрет удаления критичных строк
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Deletion is not allowed';
END;//
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_lookup
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  -- пример чтения справочника
  SELECT tax_rate
  INTO @r
  FROM regions
  WHERE id = NEW.region_id;
  SET NEW.tax_rate = @r;
END;//
DELIMITER ;

Типовая конструкция

DELIMITER //
CREATE TRIGGER customers_bi_tier
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  SET NEW.tier = CASE
                   WHEN NEW.ltv>=10000 THEN 'vip'
                   WHEN NEW.ltv>=3000  THEN 'gold'
                   ELSE 'standard'
                 END;
END;//
DELIMITER ;

100 примеров

1. Нормализация email к нижнему регистру

DELIMITER //
CREATE TRIGGER users_bi_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(NEW.email);
END;//
DELIMITER ;

2. Обрезка пробелов в логине

DELIMITER //
CREATE TRIGGER users_bi_trim_login
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.login = TRIM(NEW.login);
END;//
DELIMITER ;

3. Дефолт display_name по имени

DELIMITER //
CREATE TRIGGER profiles_bi_display
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  IF NEW.display_name IS NULL OR NEW.display_name='' THEN
    SET NEW.display_name = NEW.first_name;
  END IF;
END;//
DELIMITER ;

4. Автозаполнение slug из title

DELIMITER //
CREATE TRIGGER posts_bi_slug
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.slug = REPLACE(LOWER(NEW.title),' ','-');
END;//
DELIMITER ;

5. Гарантия положительной цены

DELIMITER //
CREATE TRIGGER products_bi_price
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  SET NEW.price = GREATEST(NEW.price,0);
END;//
DELIMITER ;

6. Обрезка описания до 500 символов

DELIMITER //
CREATE TRIGGER items_bi_desc
BEFORE INSERT ON items
FOR EACH ROW
BEGIN
  SET NEW.description = LEFT(NEW.description,500);
END;//
DELIMITER ;

7. Флаг активности по дате публикации

DELIMITER //
CREATE TRIGGER posts_bi_active
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.is_active = (NEW.published_at<=NOW());
END;//
DELIMITER ;

8. Нормализация телефона (только цифры)

DELIMITER //
CREATE TRIGGER contacts_bi_phone
BEFORE INSERT ON contacts
FOR EACH ROW
BEGIN
  SET NEW.phone = REGEXP_REPLACE(NEW.phone,'[^0-9]','');
END;//
DELIMITER ;

9. Чистка тегов: в нижний регистр

DELIMITER //
CREATE TRIGGER tags_bi_norm
BEFORE INSERT ON tags
FOR EACH ROW
BEGIN
  SET NEW.name = LOWER(NEW.name);
END;//
DELIMITER ;

10. Установка created_at

DELIMITER //
CREATE TRIGGER any_bi_created
BEFORE INSERT ON any_table
FOR EACH ROW
BEGIN
  SET NEW.created_at = COALESCE(NEW.created_at,NOW());
END;//
DELIMITER ;

11. Автострана по IP

DELIMITER //
CREATE TRIGGER sessions_bi_geo
BEFORE INSERT ON sessions
FOR EACH ROW
BEGIN
  SELECT country
  INTO @c
  FROM ip2country
  WHERE ip = NEW.ip;
  SET NEW.country = @c;
END;//
DELIMITER ;

12. Заполнение валюты заказа по региону

DELIMITER //
CREATE TRIGGER orders_bi_currency
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SELECT currency
  INTO @cur
  FROM regions
  WHERE id = NEW.region_id;
  SET NEW.currency = @cur;
END;//
DELIMITER ;

13. Склейка ФИО из частей

DELIMITER //
CREATE TRIGGER profiles_bi_fullname
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  SET NEW.full_name = CONCAT_WS(' ', NEW.last_name, NEW.first_name, NEW.middle_name);
END;//
DELIMITER ;

14. Гарантия минимального количества

DELIMITER //
CREATE TRIGGER stock_bi_minqty
BEFORE INSERT ON stock
FOR EACH ROW
BEGIN
  SET NEW.qty = GREATEST(NEW.qty,0);
END;//
DELIMITER ;

15. Автовыбор ставки налога

DELIMITER //
CREATE TRIGGER invoices_bi_tax
BEFORE INSERT ON invoices
FOR EACH ROW
BEGIN
  SELECT rate
  INTO @r
  FROM tax_rules
  WHERE country=NEW.country;
  SET NEW.tax_rate = COALESCE(@r,0);
END;//
DELIMITER ;

16. Нормализация username (только латиница и _)

DELIMITER //
CREATE TRIGGER users_bi_username
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.username = REGEXP_REPLACE(LOWER(NEW.username),'[^a-z0-9_]','');
END;//
DELIMITER ;

17. Дефолт timezone=UTC

DELIMITER //
CREATE TRIGGER profiles_bi_tz
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  SET NEW.tz = COALESCE(NEW.tz,'UTC');
END;//
DELIMITER ;

18. Схлопывание пробелов в названии

DELIMITER //
CREATE TRIGGER products_bi_spaces
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  SET NEW.name = REGEXP_REPLACE(TRIM(NEW.name),'[ ]+',' ');
END;//
DELIMITER ;

19. Заполнение короткого описания

DELIMITER //
CREATE TRIGGER posts_bi_excerpt
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.excerpt = COALESCE(NEW.excerpt, LEFT(NEW.body,160));
END;//
DELIMITER ;

20. Гарантия уникального slug (хвост‑счётчик)

DELIMITER //
CREATE TRIGGER posts_bi_slug_unique
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE base VARCHAR(255);
  SET base = REPLACE(LOWER(NEW.title),' ','-');
  WHILE EXISTS(
    SELECT 1
    FROM posts
    WHERE slug = IF(i=0, base, CONCAT(base,'-',i))
  ) DO
    SET i = i + 1;
  END WHILE;
  SET NEW.slug = IF(i=0, base, CONCAT(base,'-',i));
END;//
DELIMITER ;

21. Заполнение warehouse_id по адресу

DELIMITER //
CREATE TRIGGER shipments_bi_wh
BEFORE INSERT ON shipments
FOR EACH ROW
BEGIN
  SELECT id
  INTO @w
  FROM warehouses
  WHERE city=NEW.city
  ORDER BY capacity DESC
  LIMIT 1;
  SET NEW.warehouse_id = @w;
END;//
DELIMITER ;

22. Капитализация имени собственным правилом

DELIMITER //
CREATE TRIGGER profiles_bi_caps
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  SET NEW.first_name = CONCAT(UPPER(LEFT(NEW.first_name,1)), LOWER(SUBSTRING(NEW.first_name,2)));
END;//
DELIMITER ;

23. Нормализация URL (без хвоста /)

DELIMITER //
CREATE TRIGGER links_bi_norm
BEFORE INSERT ON links
FOR EACH ROW
BEGIN
  SET NEW.url = REGEXP_REPLACE(NEW.url,'/$','');
END;//
DELIMITER ;

24. Отсечение будущей даты рождения

DELIMITER //
CREATE TRIGGER profiles_bi_birth
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  IF NEW.birthdate>CURDATE() THEN
    SET NEW.birthdate = CURDATE();
  END IF;
END;//
DELIMITER ;

25. Автоперевод статуса при total=0

DELIMITER //
CREATE TRIGGER orders_bi_status
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.total=0 THEN
    SET NEW.status='free';
  END IF;
END;//
DELIMITER ;

Ещё примеры

26. Запрет отрицательной цены

DELIMITER //
CREATE TRIGGER products_bi_price_check
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  IF NEW.price<0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Price must be non-negative';
  END IF;
END;//
DELIMITER ;

27. Уникальность email по таблице

DELIMITER //
CREATE TRIGGER users_bi_unique_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF EXISTS(
    SELECT 1
    FROM users
    WHERE email = NEW.email
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Email already exists';
  END IF;
END);//
DELIMITER ;

28. Проверка длины пароля ≥ 8

DELIMITER //
CREATE TRIGGER users_bi_pwd_len
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.password_hash)<8 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Password too short';
  END IF;
END;//
DELIMITER ;

29. Запрет регистрации без согласия с правилами

DELIMITER //
CREATE TRIGGER users_bi_terms
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.accepted_terms<>1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Terms must be accepted';
  END IF;
END;//
DELIMITER ;

30. Проверка доступности товара на складе при заказе

DELIMITER //
CREATE TRIGGER order_items_bi_stock
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  SELECT qty
  INTO @q
  FROM stock
  WHERE product_id=NEW.product_id;
  IF COALESCE(@q,0) < NEW.qty THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Insufficient stock';
  END IF;
END;//
DELIMITER ;

31. Запрет удаления админ‑пользователя

DELIMITER //
CREATE TRIGGER users_bd_protect_admin
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  IF OLD.role='admin' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Cannot delete admin';
  END IF;
END;//
DELIMITER ;

32. Контроль формата телефона

DELIMITER //
CREATE TRIGGER contacts_bi_phone_check
BEFORE INSERT ON contacts
FOR EACH ROW
BEGIN
  IF NEW.phone NOT REGEXP '^[0-9]{10,15}$' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Invalid phone';
  END IF;
END;//
DELIMITER ;

33. Ограничение скидки ≤ 50%

DELIMITER //
CREATE TRIGGER orders_bi_discount_cap
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.discount>0.5 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Discount too high';
  END IF;
END;//
DELIMITER ;

34. Запрет отрицательного остатка при UPDATE

DELIMITER //
CREATE TRIGGER stock_bu_nonneg
BEFORE UPDATE ON stock
FOR EACH ROW
BEGIN
  IF NEW.qty<0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Negative qty not allowed';
  END IF;
END;//
DELIMITER ;

35. Проверка возраста ≥ 18

DELIMITER //
CREATE TRIGGER profiles_bi_age
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  IF TIMESTAMPDIFF(YEAR, NEW.birthdate, CURDATE()) < 18 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Age must be 18+';
  END IF;
END;//
DELIMITER ;

36. Запрет смены primary email на пустой

DELIMITER //
CREATE TRIGGER users_bu_email_not_null
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.email IS NULL OR NEW.email='' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Email is required';
  END IF;
END;//
DELIMITER ;

37. Проверка ссылочной целостности без FK

DELIMITER //
CREATE TRIGGER orders_bi_customer_exists
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NOT EXISTS(
    SELECT 1
    FROM customers
    WHERE id = NEW.customer_id
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Customer not found';
  END IF;
END;//
DELIMITER ;

38. Запрет уменьшать сумму выплаченного платежа

DELIMITER //
CREATE TRIGGER payments_bu_no_decrease
BEFORE UPDATE ON payments
FOR EACH ROW
BEGIN
  IF NEW.amount < OLD.amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Amount decrease forbidden';
  END IF;
END;//
DELIMITER ;

39. Блокировка удаления оплаченного счета

DELIMITER //
CREATE TRIGGER invoices_bd_paid_protect
BEFORE DELETE ON invoices
FOR EACH ROW
BEGIN
  IF OLD.status='paid' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Cannot delete paid invoice';
  END IF;
END;//
DELIMITER ;

40. Согласованность валюты строки заказа с шапкой

DELIMITER //
CREATE TRIGGER order_items_bi_currency_match
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  SELECT currency
  INTO @c
  FROM orders
  WHERE id = NEW.order_id;
  IF @c<>NEW.currency THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Currency mismatch';
  END IF;
END;//
DELIMITER ;

41. Запрет изменения readonly поля

DELIMITER //
CREATE TRIGGER users_bu_readonly_created
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.created_at<>OLD.created_at THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='created_at is readonly';
  END IF;
END;//
DELIMITER ;

42. Проверка лимита позиций в заказе

DELIMITER //
CREATE TRIGGER order_items_bi_limit
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  SELECT COUNT(*)
  INTO @n
  FROM order_items
  WHERE order_id=NEW.order_id;
  IF @n>=100 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Too many items';
  END IF;
END;//
DELIMITER ;

43. Запрет повторной подписки активному клиенту

DELIMITER //
CREATE TRIGGER subs_bi_no_dup
BEFORE INSERT ON subscriptions
FOR EACH ROW
BEGIN
  IF EXISTS(
    SELECT 1
    FROM subscriptions
    WHERE user_id=NEW.user_id AND status='active'
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Already subscribed';
  END IF;
END;//
DELIMITER ;

44. Блокировка смены owner у проекта

DELIMITER //
CREATE TRIGGER projects_bu_lock_owner
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
  IF NEW.owner_id<>OLD.owner_id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Owner change forbidden';
  END IF;
END;//
DELIMITER ;

45. Порог минимальной суммы заказа

DELIMITER //
CREATE TRIGGER orders_bi_min_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.total<10 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Order total too low';
  END IF;
END;//
DELIMITER ;

Ещё примеры

46. Установка updated_at

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

47. Пересчёт поля total перед INSERT

DELIMITER //
CREATE TRIGGER orders_bi_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.total = COALESCE(NEW.subtotal,0) - COALESCE(NEW.discount,0) + COALESCE(NEW.shipping,0);
END;//
DELIMITER ;

48. Расчёт НДС от суммы

DELIMITER //
CREATE TRIGGER invoices_bi_vat
BEFORE INSERT ON invoices
FOR EACH ROW
BEGIN
  SET NEW.vat = ROUND(NEW.amount * NEW.tax_rate, 2);
END;//
DELIMITER ;

49. Заполнение checksum строки

DELIMITER //
CREATE TRIGGER rows_bi_checksum
BEFORE INSERT ON rows
FOR EACH ROW
BEGIN
  SET NEW.checksum = MD5(CONCAT_WS('#',NEW.c1,NEW.c2,NEW.c3));
END;//
DELIMITER ;

50. Автогенерация referral_code

DELIMITER //
CREATE TRIGGER users_bi_ref
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.referral_code = SUBSTRING(REPLACE(UUID(),'-',''),1,10);
END;//
DELIMITER ;

51. Пре‑расчёт search_index

DELIMITER //
CREATE TRIGGER posts_bi_search
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.search_index = CONCAT(LOWER(NEW.title),' ',LOWER(NEW.body));
END;//
DELIMITER ;

52. Подбор сегмента клиента по ARPU

DELIMITER //
CREATE TRIGGER customers_bu_segment
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
  SET NEW.segment = CASE
                      WHEN NEW.arpu>=100 THEN 'pro'
                      WHEN NEW.arpu>=20  THEN 'plus'
                      ELSE 'free'
                    END;
END;//
DELIMITER ;

53. Расчёт due_at от created_at и sla_hours

DELIMITER //
CREATE TRIGGER tickets_bi_due
BEFORE INSERT ON tickets
FOR EACH ROW
BEGIN
  SET NEW.due_at = DATE_ADD(COALESCE(NEW.created_at,NOW()), INTERVAL NEW.sla_hours HOUR);
END;//
DELIMITER ;

54. Геохеш по широте/долготе

DELIMITER //
CREATE TRIGGER places_bi_geohash
BEFORE INSERT ON places
FOR EACH ROW
BEGIN
  SET NEW.geohash = ST_AsText(POINT(NEW.lon, NEW.lat));
END;//
DELIMITER ;

55. Формирование display_address

DELIMITER //
CREATE TRIGGER addresses_bi_display
BEFORE INSERT ON addresses
FOR EACH ROW
BEGIN
  SET NEW.display = CONCAT_WS(', ', NEW.street, NEW.city, NEW.country);
END;//
DELIMITER ;

56. Счётчик слов в статье

DELIMITER //
CREATE TRIGGER posts_bi_wc
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.word_count = LENGTH(NEW.body) - LENGTH(REPLACE(NEW.body,' ','')) + 1;
END;//
DELIMITER ;

57. Проставить shipped=1 при наличии tracking

DELIMITER //
CREATE TRIGGER orders_bu_shipped
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.tracking IS NOT NULL AND NEW.tracking<>'' THEN
    SET NEW.shipped=1;
  END IF;
END;//
DELIMITER ;

58. Определить приоритет тикета по ключевым словам

DELIMITER //
CREATE TRIGGER tickets_bi_prio
BEFORE INSERT ON tickets
FOR EACH ROW
BEGIN
  SET NEW.priority = CASE
                        WHEN NEW.title LIKE '%urgent%' OR NEW.body LIKE '%urgent%' THEN 'P1'
                        WHEN NEW.title LIKE '%high%'  OR NEW.body LIKE '%high%'  THEN 'P2'
                        ELSE 'P3'
                      END;
END;//
DELIMITER ;

59. Расчёт бонусов = 5% от суммы заказа

DELIMITER //
CREATE TRIGGER orders_bi_bonus
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.bonus_points = ROUND(NEW.total*0.05);
END;//
DELIMITER ;

60. Приведение значения сенсора к диапазону

DELIMITER //
CREATE TRIGGER sensors_bi_norm
BEFORE INSERT ON sensors
FOR EACH ROW
BEGIN
  SET NEW.value = LEAST(GREATEST(NEW.value, NEW.min_value), NEW.max_value);
END;//
DELIMITER ;

61. Синхронизация поля search_email

DELIMITER //
CREATE TRIGGER users_bu_search_email
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.search_email = LOWER(NEW.email);
END;//
DELIMITER ;

62. Расклейка тегов в поле keywords

DELIMITER //
CREATE TRIGGER posts_bi_keywords
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.keywords = REPLACE(LOWER(NEW.tags), ';', ',');
END;//
DELIMITER ;

63. Выбор валюты счета по клиенту

DELIMITER //
CREATE TRIGGER invoices_bi_currency
BEFORE INSERT ON invoices
FOR EACH ROW
BEGIN
  SELECT currency
  INTO @c
  FROM customers
  WHERE id=NEW.customer_id;
  SET NEW.currency = @c;
END;//
DELIMITER ;

64. Заполнение owner_id из auth_context

DELIMITER //
CREATE TRIGGER projects_bi_owner
BEFORE INSERT ON projects
FOR EACH ROW
BEGIN
  SET NEW.owner_id = COALESCE(NEW.owner_id, @current_user_id);
END;//
DELIMITER ;

65. Авторасчёт периода подписки

DELIMITER //
CREATE TRIGGER subs_bi_period
BEFORE INSERT ON subscriptions
FOR EACH ROW
BEGIN
  SET NEW.valid_till = DATE_ADD(COALESCE(NEW.valid_from, CURDATE()), INTERVAL NEW.months MONTH);
END;//
DELIMITER ;

66. Формирование канонического домена

DELIMITER //
CREATE TRIGGER sites_bi_canon
BEFORE INSERT ON sites
FOR EACH ROW
BEGIN
  SET NEW.canonical = LOWER(REGEXP_REPLACE(NEW.host,'^www\.',''));
END;//
DELIMITER ;

67. Предзаполнение поля initials

DELIMITER //
CREATE TRIGGER profiles_bi_initials
BEFORE INSERT ON profiles
FOR EACH ROW
BEGIN
  SET NEW.initials = CONCAT(LEFT(NEW.first_name,1), LEFT(NEW.last_name,1));
END;//
DELIMITER ;

68. Расчёт rating_weight из reviews_count

DELIMITER //
CREATE TRIGGER products_bu_weight
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SET NEW.rating_weight = LOG10(1+NEW.reviews_count);
END;//
DELIMITER ;

69. Расчёт unit_price из total/qty

DELIMITER //
CREATE TRIGGER order_items_bi_unit_price
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  SET NEW.unit_price = NEW.total/NULLIF(NEW.qty,0);
END;//
DELIMITER ;

70. Расчёт checksum адреса доставки

DELIMITER //
CREATE TRIGGER orders_bi_addr_crc
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.addr_crc = MD5(CONCAT_WS('|',NEW.country,NEW.city,NEW.street,NEW.zip));
END;//
DELIMITER ;

Ещё примеры

71. Автозаполнение language по Accept-Language

DELIMITER //
CREATE TRIGGER sessions_bi_lang
BEFORE INSERT ON sessions
FOR EACH ROW
BEGIN
  SET NEW.lang = SUBSTRING_INDEX(NEW.accept_language,',',1);
END;//
DELIMITER ;

72. Запрет по времени суток (окно обслуживания)

DELIMITER //
CREATE TRIGGER maintenance_bd
BEFORE DELETE ON critical_table
FOR EACH ROW
BEGIN
  IF HOUR(NOW()) BETWEEN 2 AND 4 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Maintenance window';
  END IF;
END;//
DELIMITER ;

73. Блокировка UPDATE закрытых тикетов

DELIMITER //
CREATE TRIGGER tickets_bu_closed
BEFORE UPDATE ON tickets
FOR EACH ROW
BEGIN
  IF OLD.status='closed' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Ticket is closed';
  END IF;
END;//
DELIMITER ;

74. Нельзя понижать роль администратора

DELIMITER //
CREATE TRIGGER users_bu_role_guard
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.role='admin' AND NEW.role<>'admin' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Cannot demote admin';
  END IF;
END;//
DELIMITER ;

75. Запрет смены customer_id у заказа

DELIMITER //
CREATE TRIGGER orders_bu_lock_customer
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.customer_id<>OLD.customer_id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='customer_id is immutable';
  END IF;
END;//
DELIMITER ;

76. Поле paid_at только при paid=1

DELIMITER //
CREATE TRIGGER orders_bu_paid_at
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.paid=1 AND OLD.paid=0 THEN
    SET NEW.paid_at = NOW();
  ELSEIF NEW.paid=0 THEN
    SET NEW.paid_at = NULL;
  END IF;
END;//
DELIMITER ;

77. Лимит частоты изменений профиля

DELIMITER //
CREATE TRIGGER profiles_bu_rate_limit
BEFORE UPDATE ON profiles
FOR EACH ROW
BEGIN
  IF TIMESTAMPDIFF(MINUTE, OLD.updated_at, NOW())<1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Too frequent updates';
  END IF;
END;//
DELIMITER ;

78. Запрет DELETE для сотрудников с активными задачами

DELIMITER //
CREATE TRIGGER employees_bd_tasks
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  IF EXISTS(
    SELECT 1
    FROM tasks
    WHERE assignee_id=OLD.id
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Has tasks';
  END IF;
END;//
DELIMITER ;

79. Обязательный country для клиентов EU

DELIMITER //
CREATE TRIGGER customers_bi_country
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  IF NEW.region='EU' AND (NEW.country IS NULL OR NEW.country='') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Country required for EU';
  END IF;
END;//
DELIMITER ;

80. Контроль маски email домена

DELIMITER //
CREATE TRIGGER users_bi_corp_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.email NOT LIKE '%@company.com' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Use @company.com email';
  END IF;
END;//
DELIMITER ;

81. Запрет удаления транзакций старше 24 часов

DELIMITER //
CREATE TRIGGER tx_bd_retention
BEFORE DELETE ON transactions
FOR EACH ROW
BEGIN
  IF TIMESTAMPDIFF(HOUR, OLD.created_at, NOW())>24 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Retention policy';
  END IF;
END;//
DELIMITER ;

82. Логика дат начала/окончания подписки

DELIMITER //
CREATE TRIGGER subs_bi_dates
BEFORE INSERT ON subscriptions
FOR EACH ROW
BEGIN
  IF NEW.valid_till<=NEW.valid_from THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='valid_till must be after valid_from';
  END IF;
END];//
DELIMITER ;

83. Защита от дублей платежа по checksum

DELIMITER //
CREATE TRIGGER payments_bi_dup
BEFORE INSERT ON payments
FOR EACH ROW
BEGIN
  SET @crc = MD5(CONCAT_WS(':',NEW.user_id,NEW.amount,NEW.currency,NEW.ext_id));
  IF EXISTS(
    SELECT 1
    FROM payments
    WHERE checksum=@crc
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Duplicate payment';
  END IF;
  SET NEW.checksum = @crc;
END;//
DELIMITER ;

84. Запрет обновления цен каталога в выходные

DELIMITER //
CREATE TRIGGER products_bu_weekend_lock
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF WEEKDAY(NOW()) IN (5,6) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Weekend lock';
  END IF;
END;//
DELIMITER ;

85. Проверка уникальности username (case‑insensitive)

DELIMITER //
CREATE TRIGGER users_bi_uname_ci
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF EXISTS(
    SELECT 1
    FROM users
    WHERE LOWER(username)=LOWER(NEW.username)
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Username exists';
  END IF;
END;//
DELIMITER ;

86. Запрет смены валюты счета после оплаты

DELIMITER //
CREATE TRIGGER invoices_bu_currency_lock
BEFORE UPDATE ON invoices
FOR EACH ROW
BEGIN
  IF OLD.status='paid' AND NEW.currency<>OLD.currency THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Currency locked after payment';
  END IF;
END];//
DELIMITER ;

87. Контроль формата ISO даты

DELIMITER //
CREATE TRIGGER events_bi_date_iso
BEFORE INSERT ON events
FOR EACH ROW
BEGIN
  IF NEW.date_txt NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Invalid date format';
  END IF;
END;//
DELIMITER ;

88. Блокировка удаления аккаунта с балансом

DELIMITER //
CREATE TRIGGER wallets_bd_balance
BEFORE DELETE ON wallets
FOR EACH ROW
BEGIN
  IF OLD.balance<>0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Non-zero balance';
  END IF;
END];//
DELIMITER ;

89. Запрет изменения внешнего ID

DELIMITER //
CREATE TRIGGER ext_bu_lock_external_id
BEFORE UPDATE ON external_map
FOR EACH ROW
BEGIN
  IF NEW.external_id<>OLD.external_id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='external_id immutable';
  END IF;
END];//
DELIMITER ;

90. Обязательный индекс качества данных

DELIMITER //
CREATE TRIGGER leads_bi_quality
BEFORE INSERT ON leads
FOR EACH ROW
BEGIN
  IF COALESCE(NEW.email,'')='' AND COALESCE(NEW.phone,'')='' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Email or phone required';
  END IF;
END];//
DELIMITER ;

Ещё примеры

91. Шаблон: upsert‑нормализация перед INSERT

DELIMITER //
CREATE TRIGGER any_bi_norm_template
BEFORE INSERT ON any_table
FOR EACH ROW
BEGIN
  SET NEW.key = LOWER(TRIM(NEW.key));
  IF NEW.key='' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Key required';
  END IF;
END;//
DELIMITER ;

92. Шаблон: soft‑delete блок вместо DELETE

DELIMITER //
CREATE TRIGGER any_bd_soft
BEFORE DELETE ON any_table
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Use soft delete';
END];//
DELIMITER ;

93. Шаблон: audit updated_by

DELIMITER //
CREATE TRIGGER any_bu_audit_user
BEFORE UPDATE ON any_table
FOR EACH ROW
BEGIN
  SET NEW.updated_by = COALESCE(@current_user_id, NEW.updated_by);
END];//
DELIMITER ;

94. Шаблон: денормализация счётчиков

DELIMITER //
CREATE TRIGGER comments_bi_counter
BEFORE INSERT ON comments
FOR EACH ROW
BEGIN
  SET NEW.body = TRIM(NEW.body);
END];//
DELIMITER ;

95. Шаблон: сигнал с кодом и подробностями

DELIMITER //
CREATE TRIGGER orders_bi_signal_detail
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.total<=0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='Bad total', MYSQL_ERRNO=1644;
  END IF;
END];//
DELIMITER ;

Ещё примеры

96. Шаблон: защита от нулевого делителя

DELIMITER //
CREATE TRIGGER stats_bi_guard
BEFORE INSERT ON stats
FOR EACH ROW
BEGIN
  IF NEW.den=0 THEN
    SET NEW.den = 1;
  END IF;
END];//
DELIMITER ;

97. Шаблон: нормализация JSON

DELIMITER //
CREATE TRIGGER meta_bi_json
BEFORE INSERT ON meta
FOR EACH ROW
BEGIN
  SET NEW.payload = JSON_REMOVE(NEW.payload,'$.debug');
END];//
DELIMITER ;

98. Шаблон: обрезка HTML

DELIMITER //
CREATE TRIGGER posts_bi_strip_html
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.body = REGEXP_REPLACE(NEW.body,'<[^>]+>','');
END];//
DELIMITER ;

99. Шаблон: защитить parent_id от циклов (простой чек)

DELIMITER //
CREATE TRIGGER cats_bu_no_self
BEFORE UPDATE ON categories
FOR EACH ROW
BEGIN
  IF NEW.parent_id=OLD.id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Self as parent';
  END IF;
END];//
DELIMITER ;

100. Шаблон: нормализация валютного кода

DELIMITER //
CREATE TRIGGER money_bi_ccy
BEFORE INSERT ON money
FOR EACH ROW
BEGIN
  SET NEW.currency = UPPER(LEFT(NEW.currency,3));
END];//
DELIMITER ;

BEFORE в MySQL. Заключение

BEFORE‑триггеры позволяют раннее вмешательство в поток данных: чистить и нормализовать значения, безопасно рассчитывать derived‑поля, а также жестко останавливать ошибочные операции через SIGNAL. Следите за простотой логики и избегайте тяжёлых запросов внутри триггеров.

Справка: официальная документация MySQL.

 

0 BEFORE MySQL
Понравилась статья? Поделиться с друзьями:
Вам также может быть интересно