MySQL trigger before Insert value Checking

Question

I have a table staff with office column. Currently the office column do not accept NULL values. The application persisting onto this table has a bug which meant that, when the staff has not been assigned an office, it tries inserting a NULL value onto the table.

I have been asked to used a trigger to intercept the insert onto the Staff table and check if the office value is NULL and replace it with value N/A.

Below is my attempt so far, but do have error in attempt to implement. Any Ideas on how to resolve this.

CREATE TRIGGER staffOfficeNullReplacerTrigger BEFORE INSERT ON Staff
  FOR EACH ROW BEGIN
    IF (NEW.office IS NULL)
     INSERT INTO Staff SET office="N/A";
    END IF
  END;

The error:

MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Staff SET office="N/A"; END'

Answer 1

First, alter the table to allow NULLs:

ALTER TABLE Staff MODIFY office CHAR(40) DEFAULT "N/A";

(Change CHAR(40) to whatever is appropriate.) Then you could use as your trigger:

CREATE TRIGGER staffOfficeNullReplacerTrigger 
BEFORE INSERT 
ON Staff
  FOR EACH ROW BEGIN
    IF (NEW.office IS NULL) THEN
      SET NEW.office = "N/A";
    END IF