插入值检查前的 MySQL 触发器

2022-01-01 00:00:00 mysql triggers

我有一个带有 office 列的表 staff.目前 office 列不接受 NULL 值.持久保存在该表上的应用程序有一个错误，这意味着当员工没有被分配到办公室时，它会尝试在表中插入一个 NULL 值.

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.

我被要求使用触发器拦截插入到 Staff 表中并检查 office 值是否为 NULL 并将其替换为值 N/A.

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.

以下是我迄今为止的尝试，但在尝试实施时确实存在 error.关于如何解决此问题的任何想法.

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;

错误:

MySQL 数据库错误:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册在INSERT INTO Staff SET office="N/A"附近使用的语法；结束'

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'

推荐答案

首先，修改表以允许 NULL:

First, alter the table to allow NULLs:

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

(将 CHAR(40) 更改为任何合适的值.)然后您可以将其用作触发器:

(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

相关文章