mysql 插入触发,MySQL在插入值检查之前触发

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'

解决方案

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