Use a Trigger to block an Insert

from the Artful MySQL Tips List

Since version 5.5, MySQL's support for SIGNAL allows Triggers to block Inserts:
drop table if exists foo;
create table foo( v varchar(32) );
drop trigger if exists foo_ins;
delimiter go
create trigger foo_ins before insert on foo
for each row
 if new.v = '' then
  signal sqlstate '45000' 
  set message_text="Your Insert was blocked.";
 end if;
delimiter ;
insert into foo values(""); -- ERROR 1644 (45000): Your Insert was blocked.
insert into foo values ("abc");
select * from foo;
| v    |
| abc  |

Last updated 8 Mar 2017

Return to the Artful MySQL Tips page