C, PHP, VB, .NET

Към версия 5.5 MySQL продължава да НЕ поддържа CHECK ограничения. В предишна статия показахме как може да се симулира CHECK с VIEW. С версия 5.5 на MySQL вече може да се използват т.нар. SIGNALS в тригери и съхранени процедури, което ни позволява „да хвърляме грешки“ от тях.

Пример 1: Нека покажем един пример – имаме елементарна таблица, в която записваме данните за потребители, каквато например използваме за примера за скрипт за автентикация в раздел ПТСК:

CREATE TABLE users(
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(32) NOT NULL,
 password CHAR(64) NOT NULL
)ENGINE=InnoDB;

Искаме да направим ограничение върху потребителското име, а именно – да бъде между 4 и 32 символа. Горната граница е ясно определена от типа на полето – varchar(32). По подразбиране ако се опитате да вмъкнете повече информация от предвидената, то тя ще бъде отрязана до 32 символ и вмъкната по този начин, а заявката ще бъде успешна, но със съответния „warning“ (станал е „truncate“ на низа). Ако MySQL е настроен да работи в режим с включен „STRICT_ALL_TABLES“ (прочетете повече за режимите на MYSQL), то ще се генерира грешка.

Долната граница обаче не е лимитирана по никакъв начин. Искаме да направим така, че да се генерира грешка ако се опитаме да вмъкнем потребителско име по-късо от 4 символа. Ето как можем да го направим с тригер в MySQL 5.5 (и евентуално по същия начин в по-новите версии):

DELIMITER //

CREATE TRIGGER username_min_length_check
BEFORE INSERT ON users FOR EACH ROW
BEGIN
 DECLARE errormsg VARCHAR(255);
 IF CHAR_LENGTH(NEW.username) < 4
 THEN SET errormsg = CONCAT("Username too short: ", NEW.username);
      SIGNAL SQLSTATE '45000' SET message_text = errormsg;
 END IF;
END//

DELIMITER ;

SQL State със стойност 45000 има смисъл като „unhandled user-defined exception“, което е правилно да се използва за такива случаи. Ето две пробни заявки, които показват, че тригерът работи както очаквахме:

mysql> INSERT INTO users(username, password)
    -> VALUES ("user123", SHA2("password", 256));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users(username, password)
    -> VALUES ("aa", SHA2("password", 256));
ERROR 1644 (45000): Username too short: aa

Разбира се по-добре ще бъде да валидирате данните в самото приложение и въобще да не изпълнявате заявката ако потребител се опитва да се регистрира с по-къс низ за username. Ако обаче нивата на сигурност го изискват, то може да си позволите двойна проверка.

Ако системата евентуално позволява промяна на потребителско име, то може да направите такъв тригер и за UPDATE заявки.

Внимание: С показания тригер при вмъкването на множество редове накуп ако дори само един от тях предизвика въпросната грешка, то тя ще важи за цялата заявка и нито един от редовете няма да бъде вмъкнат, дори някои от тях да отговарят на условието.

Пример 2: Нека имаме две таблици – отдели и служители. Между тях ще има две връзки. Едната ще има смисъл, че „даден служител работи в даден отдел“, а другата „даден служител е мениджър на даден отдел“:

CREATE TABLE departments(
 id TINYINT UNSIGNED PRIMARY KEY,
 name VARCHAR(255) NOT NULL UNIQUE,
 manager INT UNSIGNED NULL DEFAULT NULL UNIQUE
)ENGINE = InnoDB;

CREATE TABLE employees(
 id INT UNSIGNED PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 d_id TINYINT UNSIGNED,
 FOREIGN KEY (d_id) REFERENCES departments(id)
    ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = InnoDB;

Сега създаваме два тригера – един за INSERT и един за UPDATE заявки, в които проверяваме дали стойността на „manager“ в таблица „departments“ е валидна:

DELIMITER //

CREATE TRIGGER managercheck_insert
BEFORE INSERT ON departments FOR EACH ROW
BEGIN
  IF NEW.manager IS NOT NULL
  THEN SIGNAL SQLSTATE '45000'
       SET message_text = "Please insert the dept with manager NULL";
  END IF;
END//

CREATE TRIGGER managercheck_update
BEFORE UPDATE ON departments FOR EACH ROW
BEGIN
  IF NEW.manager IS NOT NULL
     AND
     NEW.manager NOT IN (SELECT employees.id
                         FROM employees
                         WHERE employees.d_id = NEW.id)
  THEN SIGNAL SQLSTATE '45000'
       SET message_text = "The manager must be employee from the department";
  END IF;
END//

DELIMITER ;

Вмъкваме примерни данни:

INSERT INTO departments (id, name, manager)
VALUES (1, "Support", NULL),
       (2, "Sales", NULL);

INSERT INTO employees(id, name, d_id)
VALUES (1, "Petar", 1),
       (2, "Ivan", 1),
       (3, "Maria", 2);

И изпробваме дали тригерите работят:

mysql> UPDATE departments
    -> SET manager = 2
    -> WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE departments
    -> SET manager = 3
    -> WHERE id = 1;
ERROR 1644 (45000): The manager must be employee from the same department

mysql> INSERT INTO departments(id, name, manager)
    -> VALUES (3, "Billing", 2);
ERROR 1644 (45000): Please insert the dept with manager NULL

Забележете, че по този начин дори не се налага да правим колоната „manager“ като foreign key към employees. В действителност написаният тригер припокрива тази функционалност. Ето какво ще се случи ако се опитаме да въведем несъществуваща стойност в тази колона:

mysql> INSERT INTO departments(id, name, manager)
    -> VALUES (3, "Billing", 12);
ERROR 1644 (45000): Please insert the dept with manager NULL