CODEWITHSUNDEEP
mysqlsqltriggers
user1032531
user1032531

Reputation: 26281

Using BEFORE UPDATE ON to update a column value

I have a table as described below, and am using BEFORE INSERT triggers to update a column's value to a session defined variable. The INSERT set's the modified_by_user and created_by_user columns both to 1 as expected. The UPDATE, however, does not change modified_by_user to 2 as I expected, but leaves it at 1. How can BEFORE UPDATE ON be used to update a column value? I am using MySQL 5.5. Thanks

SET @users_id=1;
INSERT INTO notes(notes,record_status_id,date_created) VALUES("hello","active",NOW());
//modified_by_user=1, created_by_user=1, notes="hello"

SET @users_id=2;
UPDATE notes SET notes="goodby" WHERE id=1;
//modified_by_user=1, created_by_user=1, notes="goodby"

CREATE  TABLE IF NOT EXISTS `notes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `notes` TEXT NULL ,
  `record_status_id` VARCHAR(8) NOT NULL ,
  `date_created` DATETIME NOT NULL ,
  `date_modified` TIMESTAMP NOT NULL ,
  `modified_by_user` INT UNSIGNED NULL ,
  `created_by_user` INT UNSIGNED NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_notes_users1_idx` (`modified_by_user` ASC) ,
  INDEX `fk_notes_users2_idx` (`created_by_user` ASC) ,
  INDEX `fk_notes_record_status1_idx` (`record_status_id` ASC) ,
  CONSTRAINT `fk_notes_users10`
    FOREIGN KEY (`modified_by_user` )
    REFERENCES `users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_notes_users20`
    FOREIGN KEY (`created_by_user` )
    REFERENCES `users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_notes_record_status1`
    FOREIGN KEY (`record_status_id` )
    REFERENCES `record_status` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

DELIMITER $$


CREATE TRIGGER tg_notes_ins BEFORE INSERT ON notes FOR EACH ROW
BEGIN
    IF NEW.created_by_user IS NULL OR NEW.created_by_user = '' THEN
        SET NEW.created_by_user = @users_id;
        SET NEW.modified_by_user = @users_id;
    END IF;
END$$



CREATE TRIGGER tg_notes_upd BEFORE UPDATE ON notes FOR EACH ROW
BEGIN
    IF NEW.modified_by_user IS NULL OR NEW.modified_by_user = '' THEN
        SET NEW.modified_by_user = @users_id;
    END IF;
END$$


DELIMITER ;

Upvotes: 1

Views: 141

Answers (1)

user1032531
user1032531

Reputation: 26281

On an UPDATE, if the query doesn't specify a value for a given column NEW.column is not NULL as I originally expected, but is the value of that column should it not be changing (i.e. the previous OLD.column value)

Upvotes: 1

Related Questions