MySQL create "before insert" trigger failing and raising errors in triggers

Question

I have successfully created an insert before trigger on a table using the innodb engine which "throws" an error by performing an insert on a non-existent table. The problem is that as I try to run the database create script on the production server it fails because the insert table does not exist. However, the same script runs fine on my workstation which makes me think that there is a MySQL config setting which is causing the create script to fail.

The question my issue brings up is whether the production server is compiling the trigger and my workstation is not (or compiling at runtime). On a production environment I would prefer to have the SQL compiled when created.

Answer 1

My method for simulating raising an error in a trigger is to DECLARE an integer variable and store a string in it. If you have SQL_MODE = STRICT_ALL_TABLES, this raises an error. Otherwise it raises a warning.

use test;

drop table if exists foo;
create table foo (id serial primary key, bar varchar(10));

drop trigger if exists RaiseError;

delimiter //

create trigger RaiseError before insert on foo
for each row
begin
  declare bar int;
  if new.bar = 'boom' then
    set bar = 'You cannot store that value!';
  end if;
end //

delimiter ;

set sql_mode = strict_all_tables;

insert into foo (bar) values ('boom');

Notice I named my local variable the same as the column bar that I want to test. That way the name of the variable appears in the error message, it's the same as the column:

ERROR 1366 (HY000): Incorrect integer value: 'You cannot store that value!' for column 'bar' at row 1