MariaDB trigger with IF-statement doesn't work and returns no errors

Question

I wrote a rather simple trigger in MariaBD 10.11.2, but it doesn't produce any output, not even an error.

CREATE TRIGGER Automatic_Employee
BEFORE INSERT
ON Test_Results
FOR EACH ROW
BEGIN
  IF (CURRENT_USER() = 'FOO') THEN
    SET NEW.Employee_ID = 0;
  END IF;
END

This is the code in question. I have similar triggers for this table, where part of the results is entered automatically. I am logged in as FOO and the field remains [NULL] instead of 0 after I enter my input. Everything else works.

Any ideas why? Thanks in advance.

Answer 1

Your code should give you an error about unknown Employee_ID.

To set the value for a column in inserted rows, you use notation new.column.

Your code should look like this:

CREATE TRIGGER Automatic_Employee
BEFORE INSERT
ON Test_Results
FOR EACH ROW
BEGIN
  IF (USER() = 'FOO@localhost') THEN
    SET new.Employee_ID = 0;
  END IF;
END

The CURRENT_USER() function returns the CREATOR in stored routines and in triggers. The USER() function returns the username and the host name for the user running the query.