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'

Why not change the schema so that NULL values are permitted? This is exactly what NULL should be used for, as it enables one to differentiate between no office and an office which happens to have the value of N/A (whereas your proposed solution would not). — eggyal
– eggyal, Commented Oct 11, 2012 at 9:28

unutbu · Accepted Answer · 2012-10-11 10:29:10Z

25

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

answered Oct 11, 2012 at 10:29

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

galaxyAbstractor · Accepted Answer · 2012-10-11 09:26:14Z

4

Shouldn't it be something like this:

DELIMITER $$

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

answered Oct 11, 2012 at 9:26

galaxyAbstractor

5571 gold badge4 silver badges13 bronze badges

Comments

Roman Marusyk · Accepted Answer · 2015-09-02 14:22:44Z

2

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

; add semi colon after END IF

  END;

edited Sep 2, 2015 at 14:22

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

answered Oct 11, 2012 at 10:36

DEVEX

13711 bronze badges

Collectives™ on Stack Overflow

MySQL trigger before Insert value Checking

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related