CODEWITHSUNDEEP
mysqltriggers
Bitmap
Bitmap

Reputation: 12538

MySQL trigger before Insert value Checking

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'

Upvotes: 17

Views: 55534

Answers (3)

DEVEX
DEVEX

Reputation: 137

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;

Upvotes: 2

unutbu
unutbu

Reputation: 880937

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

Upvotes: 25

galaxyAbstractor
galaxyAbstractor

Reputation: 557

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$$

Upvotes: 4

Related Questions