Триггер, запрещающий добавление данных в конкретный столбец.

[vedro-compota]

Здравствуйте уважаемые программисты)
Скажите пожалуйста - надо ли ограничивать изменение данных столбца, который указан , как AUTO INCREMENT (чтобы туда нельзя было подставить произвольное значение.) если да - то как создать триггер, который запрещает добавление данных в конкретный столбец ( MySQL) ?
по поводу таблицы нашёл такой скрипт -
например, есть таблица -

create table USERR
(
  USERR_ID   INT not null AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(100),
 
);

- соответственно , для ограничения изменения USERR_ID можно использовать что-то вроде того (но это не совсем правильно, кажется) -

CREATE TRIGGER before_insert_userr_id BEFORE INSERT ON USERR.USERR_ID rollback tran

-------------------
заранее благодарю)

вроде бы mysql не поддерживает прерывание исполнения триггера и народ предлагает такое вот решение -

There's no support in MySQL's current implementation of triggers to voluntarily throw an exception and abort the statement that spawned the trigger.

The workaround I've found is to write a BEFORE trigger to set one of the not-NULL columns in the table to NULL, thus violating its NOT NULL constraint. This causes the statement that spawned the trigger to be aborted.

то есть просто присвоить одному из столбцов not null типа значение null - и тем самым вызвать исключение.
но непонятно - будет ли данный тригер запускаться при автоматическом инкрементировании поля?

[mr.allty]

Скажите пожалуйста - надо ли ограничивать изменение данных столбца, который указан , как AUTO INCREMENT (чтобы туда нельзя было подставить произвольное значение.)

Можно создать такой триггер, который для любового "правильного" значения Auto_increment все равно поставит следующее значение (т.е. большее на 1)

Таблица:

CREATE TABLE `test` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(50) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Триггер:

DELIMITER $$

USE `test`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `test`.`some_trigger`
BEFORE INSERT ON `test`.`test`
FOR EACH ROW
begin
    select count(*) into @total FROM `test`.`test`;
    
    if new.user_id != (@total + 1) then 
        set new.user_id = @total + 1;
    end if;
    
end$$

Что делает этот триггер:
1. Как видно считает общее количество записей в таблице
2. Присваивает полученное значение переменной @total
3. Затем перехватывает новое поставляемое значение, при этом устанавливает его в @total + 1, то есть на 1 больше, чем предыдущее.

Результат выполения триггера:

insert into test.test (name) values ('name1');
select * FROM test.test;

insert into test.test (user_id, name) values (12, 'name2');
select * FROM test.test;

Получим результат:

row: #1 user_id: 1 name: name1
row: #2 user_id: 2 name: name2

Как видно, даже явно указывая 12, в БД вставилось значение, большее на 1 предыдущего значения.


На счет запрета вставки в определенный столбец, еще подумаю )))

Кстати приведенный мною пример триггера не много нарушает целостность первичного ключа.
То есть если мы в таблицу без моего триггера удалим последнюю запись с user_id = 3, а затем вставим новую запись, то вставиться запись с user_id = 4.
А вот в таблице с моим триггером при удалении последней записи с user_id = 3, а затем при вставке новой, вставиться запись с user_id = 3, что противоречит определению первичного ключа )))

Насчет запрета вставки значения в столбец. Мне не совсем понятно для чего Вам это нужно. Имеет ли смысл вообще создавать столбец, куда невозможно будет вставить запись. Нет столбца - нет записи.
А вот если запретить обновление существующего значения в определенном столбце, то все просто - надо в теле триггера вписать примерно

SET new.user_id = old.user_id

и в качестве события для триггера указать BEFORE UPDATE

[vedro-compota]

Насчет запрета вставки значения в столбец. Мне не совсем понятно для чего Вам это нужно. Имеет ли смысл вообще создавать столбец, куда невозможно будет вставить запись. Нет столбца - нет записи.

этот столбец -id-шник -который должен наращиваться автоматически а не вручную - то есть этот тригер не должен дейстовавть на внутренний автоикремент.....
я написал такой тригерр (кривой) - но теперь ничего в таблицу вообще добавить нельзя (( -

CREATE TRIGGER before_insert_userr_id BEFORE INSERT ON USERR FOR EACH ROW INSERT INTO USERR (USERR_ID) VALUES (NULL);

[Stilet]

Есть в мускуле возможность написания своей процедуры? если да - напиши процедуру вставки, а программист уже пусть юзает не INSERT вчистую, а твою процедуру, которая корректно будет вносить данные.

[mr.allty]

CREATE TRIGGER before_insert_userr_id BEFORE INSERT ON USERR FOR EACH ROW INSERT INTO USERR (USERR_ID) VALUES (NULL);

После чего выдает ошибку кода Error Code: 1442?
Для решения проблемы просто замените триггер примерно на

CREATE TRIGGER before_insert_userr_id BEFORE INSERT ON USERR 
FOR EACH ROW 
    SET new.userr_id = NULL;

[vedro-compota]

Насчет запрета вставки значения в столбец. Мне не совсем понятно для чего Вам это нужно. Имеет ли смысл вообще создавать столбец, куда невозможно будет вставить запись. Нет столбца - нет записи.

просто необходимо запретить указание id-шника (который итак AUTO_INCREMENT ) при добавлении новой строки....вот в чём проблема..

[vedro-compota]

Товарищи , подскажите правильно ли я понимаю, что при попытке самостоятельно в запросе задать userr_id (not null AUTO_INCREMENT) при наличии такого триггера -

CREATE TRIGGER before_insert_userr_id BEFORE INSERT ON USERR 
FOR EACH ROW 
    SET new.userr_id = NULL;

исключительной ситуцуии ( userr_id = NULL , когда он должен быть not null ) не возникнет так как до проверки корректности операции сработает AUTO_INCREMENT ?

[Аватар]

Триггер будет отрабатывать не зависимо от того задано или не задано в запросе значение поля и , поскольку, BEFORE INSERT, то (теоретически) до создания записи. Для полной уверенности на левой таблице нельзя что ли поэксперементировать?

[vedro-compota]

попробую....просто в выше описанном случае исключения не возникает - запись просто получает положенный ей новый id....