Search code examples
sqlmysqltriggers

MySQL BEFORE UPDATE query is not updating correctly

I have the following table and update trigger that is made of two parts - an update and a soft delete. A local variable @account_name is used along with the trigger. On a second update, trigger doesn't fire update correctly. For test purposes, I have WhoTriggered field in the table, to keep track of what part of the trigger engaged during update:

CREATE TABLE `CC` (
  `Id` char(36) NOT NULL,
  `Name` char(250) DEFAULT NULL,
  `WhoTriggered` char(250) DEFAULT NULL,
  `Active` tinyint(1) DEFAULT NULL,
  `Deleted` tinyint(1) DEFAULT NULL,
  `CreatedBy` char(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `UpdatedBy` char(100) DEFAULT NULL,
  `UpdatedAt` datetime DEFAULT NULL,
  `DeletedBy` char(100) DEFAULT NULL,
  `DeletedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$

CREATE TRIGGER before_CC_update BEFORE UPDATE ON CC
FOR EACH ROW
BEGIN
    IF new.deleted = 1 THEN
        SET NEW.WhoTriggered = 'SoftDelete';
        SET NEW.DeletedBy = @account_name;
        SET NEW.DeletedAt = CURRENT_TIMESTAMP();
    ELSE
        SET NEW.WhoTriggered = 'Update';
        SET NEW.UpdatedBy = @account_name;
        SET NEW.UpdatedAt = CURRENT_TIMESTAMP();
    END IF;
END$$
DELIMITER ;

Insertion, First Update and Marking the record as deleted works with no issues

SET @account_name = 'FirstInsert';
INSERT INTO `test`.`CC` (`Id`, `Name`, `Active`) VALUES ('123', '123', '1');

SET @account_name = 'FirstUpdate';
UPDATE `test`.`CC` SET `Name` = 'Name1' WHERE (`Id` = '123');

SET @account_name = 'MarkDeleted';
UPDATE `test`.`CC` SET `Deleted` = '1' WHERE (`Id` = '123');

enter image description here

However, I also want to track if anyone touches or tries to update record, even if it was marked as deleted, so I ran the second update:

SET @account_name = 'SecondUpdate';
UPDATE `test`.`CC` SET `Name` = 'Name2' WHERE (`Id` = '123');

enter image description here

It gives incorrect update. WhoTriggered should be Update, UpdatedBy should be SecondUpdate, DeletedBy should be MarkDeleted(it's old value). In other words, second update triggered the "deletion" part of the trigger, instead of "update" part of the query.

What is causing this issue and how to correct it ?

Solution

  • The code for your first condition will run whenever new.deleted = 1. So after you first set deleted = 1, it will always run this branch. Instead change the condition to:

    IF OLD.deleted IS NULL AND NEW.deleted = 1 THEN

    Here's a db<>fiddle.

    This is not a good use of triggers, especially with the need for your @account_name variable. It would almost certainly be better managed in your client application code. Just to put in my two-penny worth.