How can I check if the created_in column is none or not inside triggers?-CodePudding

The column and trigger structure inside the users' table

CREATE TABLE `users` (
 `created_in` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Trigger	Event	Table	Statement	Timing
...	insert	users	IF NEW.created_in = "" THEN SIGNAL SQLSTATE "45000"; END IF	before

I'm trying to cancel the insert if the created_in column was none but does not work.

How can I check if the created_in column is none or not?

Version of MySQL is 5.7

CodePudding user response：

Trigger not needed, sesstion strict mode is enough:

set session sql_mode = '';

CREATE TABLE `users` (
 `created_in` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
insert into users values ('');
select * from users;

| created_in |
| :--------- |
| 0000-00-00 |

set session sql_mode = 'STRICT_ALL_TABLES';

insert into users values ('');

Incorrect date value: '' for column 'created_in' at row 1

db<>fiddle here