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

Answer 2

It turns out the length of the table name was causing the problem. The two server versions were from different releases with the server being a previous release (5.0 vs 5.1).

As for raising errors in triggers I have taken a different approach, however one that may be flawed depending on how MySQL handles the create trigger statement. If the trigger statements are verified on creation then this will fail.

Once an error is encountered (expected error) I set a session variable (variable name prefixed by an @) with the error message to display. Once the error message is set I do an insert into the non-existing table Die to raise an error in MySQL.

The application then catches the database error and performs a query on the error session variable. Based on the result I either throw an exception with the error message, the database error message or have the query quietly fail leaving the calling code to handle the failed query.

MySQL Trigger:

CREATE TRIGGER Error_Trigger 
BEFORE INSERT ON Fubar 
FOR EACH ROW
BEGIN
    if DataIsBad then
        set @Err = 'The data is fubared.';
        insert into Die values (1);                
    end if;    
END$$

Application side Code:

public function getDatabaseErrorMessage($AdminMessage = false){
    if ($this->db->_error_number() != 0){
        $AdminError = $this->db->_error_message();
        $Query = $this->db->query("select @Err");
        if ($Query){
            $Error = $Query->row_array();
        }
        if (isset($Error["@Err"]) && $Error["@Err"] != ""){
            $Error = $Error["@Err"];
            $this->db->query("set @Err = ''");
        } else {
            if ($AdminMessage){
                $Error = $AdminError;
            } else {
                return false;
            }
        }
        throw new Exception($Error);
    }
}