REPLACE deletes a conflicting row despite a BEFORE DELETE 02TRG skip signal

A BEFORE DELETE trigger that signals SQLSTATE 02TRG preserves the row for a direct DELETE, but REPLACE removes the same protected row and inserts the replacement. The trigger runs in both paths, and the REPLACE completes without an exception.

To reproduce, run the following in one client session:

CREATE DATABASE trigger_test;

USE trigger_test;

CREATE TABLE t1 (

  id INT PRIMARY KEY,

  val INT

) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1,10);

SET @calls=0;

DELIMITER //

CREATE TRIGGER skip_delete BEFORE DELETE ON t1 FOR EACH ROW

BEGIN

  SET @calls=@calls+1;

  SIGNAL SQLSTATE '02TRG';

END//

DELIMITER ;

DELETE FROM t1 WHERE id=1;

SELECT 'direct' AS check_name,id,val FROM t1;

SET @replace_error=0;

DELIMITER //

BEGIN NOT ATOMIC

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @replace_error=1;

  REPLACE INTO t1 VALUES (1,20);

END//

DELIMITER ;

SELECT 'replace' AS check_name,id,val FROM t1;

SELECT 'trigger_calls' AS check_name,@calls;

SELECT 'replace_error' AS check_name,@replace_error;

DROP DATABASE trigger_test;

The anonymous block only catches a possible REPLACE error so that the row and trigger counter can still be inspected.

The relevant recorded values were:

direct         1  10

replace        1  20

trigger_calls  2

replace_error  0

The direct DELETE leaves (1,10) unchanged, confirming the skip outcome. The counter value 2 confirms that the BEFORE DELETE trigger also ran during duplicate-conflict handling in REPLACE. Nevertheless, REPLACE returned successfully and changed the row to (1,20).

When the BEFORE DELETE trigger signals the skip outcome, the original row should remain (1,10). The replacement may be skipped or may report a duplicate/error, but it must not physically remove the protected row before honoring that outcome.

This occurs when REPLACE resolves a duplicate-key conflict through a BEFORE DELETE trigger that signals the 02TRG row-skip outcome.