MySQL在插入值检查之前触发 [英] MySQL trigger before Insert value Checking

问题描述

我有一个带有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"附近使用的语法； END'

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

这篇关于MySQL在插入值检查之前触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！