Re: Trigger question

[Peter Brawley]

MySQL Triggers are awkward for such validation. It's usually best to validate in the application layer, in the module that runs the input form, so the user can correct such errors before the record is submitted.

If for some reason it absolutely must be done in a Trigger, and if the column is defined NOT NULL, here is one way ...

create trigger tupd before update on t for each row
begin
declare msg varchar(255);
if new.string='some bad value' then
set new.string=null; -- mysql will reject null
set msg = 'info for user';
signal sqlstate '45000' set message_text = msg; -- inform user
end if;
end;