CODEWITHSUNDEEP
mysqltriggersabort
David Rodrigues
David Rodrigues

Reputation: 12542

Cancel DELETE with TRIGGERs

I want to cancel a TRIGGER of DELETE. The idea is BEFORE DELETE, set column hide = "Y" then CANCEL the TRIGGER event and NOT really DELETE the row.

It's possible?

Upvotes: 2

Views: 4516

Answers (1)

Johan
Johan

Reputation: 76670

You can cancel the delete, however you cannot change the values in a DELETE trigger. 

DELIMITER $$

CREATE TRIGGER bd_t1_each BEFORE DELETE ON t1 FOR EACH ROW
BEGIN
  //This will not work, because the `NEW` virtual table does not exist in a delete trigger.
  SET NEW.hide = 'Y';
  //Raising an error will work.
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETE canceled'; 
END $$

DELIMITER ;

Instead you should make a stored procedure to hide your rows: 

DELIMITER $$

CREATE PROCEDURE delete_t1_row (pID INTEGER)
BEGIN
  UPDATE t1 SET hide = 'Y' WHERE id = pID;
END $$

DELIMITER ;

Upvotes: 5

Related Questions