CODEWITHSUNDEEP
mysqlsqlsyntaxtriggers
Thommy
Thommy

Reputation: 59

MySQL Create Trigger Syntax

DELIMITER // 
CREATE TRIGGER never_update_with_null
BEFORE UPDATE
ON `table_name`
FOR EACH ROW
BEGIN
    IF OLD.name IS NOT NULL AND NEW.name IS NULL THEN
        SET NEW.name = OLD.name;
    IF OLD.phone IS NOT NULL AND NEW.name IS NULL THEN
        SET NEW.phone = OLD.phone;
    IF OLD.place IS NOT NULL AND NEW.place IS NULL THEN
        SET NEW.place = OLD.place;
END; //
DELIMITER ;

I am trying to achieve the following: If someone UPDATEs a value in name, phone or place(columns) in the table table_name and its value (the updating value) is NULL then the value stays the old one (does not get updated). Maybe a little shorter: never update with NULL.

The above SQL does not create a trigger but it either does not throw any error(phpMyAdmin). Does it lack syntax? Already tried it with ENDIF; after every OLD.column; - no improvement. Thank you for explanation.

Upvotes: 1

Views: 1990

Answers (1)

Mihai
Mihai

Reputation: 26784

Try

DELIMITER // 
CREATE TRIGGER never_update_with_null
BEFORE UPDATE
ON `table_name`
FOR EACH ROW
BEGIN
    IF OLD.name IS NOT NULL AND NEW.name IS NULL THEN
        SET NEW.name = OLD.name;
    END IF;
    IF OLD.phone IS NOT NULL AND NEW.phone IS NULL THEN
        SET NEW.phone = OLD.phone;
    END IF;
    IF OLD.place IS NOT NULL AND NEW.place IS NULL THEN
        SET NEW.place = OLD.place;
    END IF;
END; //
DELIMITER ;

Upvotes: 3

Related Questions