How to write a trigger to abort delete in MYSQL?

Question

I read this article but it seems not work for delete. I got this error when tried to create a trigger:

Executing SQL script in server

ERROR: Error 1363: There is no NEW row in on DELETE trigger

CREATE TRIGGER DeviceCatalog_PreventDeletion 
BEFORE DELETE on DeviceCatalog
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF old.id = 1 or old.id =2 THEN
        SELECT * FROM DeviceCatalog WHERE DeviceCatalog.id=NEW.id;
    END IF;
END; 

SQL script execution finished: statements: 4 succeeded, 1 failed

Answer 1

Try something like this -

DELIMITER $$

CREATE TRIGGER trigger1
BEFORE DELETE
ON table1
FOR EACH ROW
BEGIN
  IF OLD.id = 1 THEN -- Abort when trying to remove this record
    CALL cannot_delete_error; -- raise an error to prevent deleting from the table
  END IF;
END
$$

DELIMITER ;

Answer 2

Improving @Devart's (accepted) answer with @MathewFoscarini's comment about MySQL SIGNAL Command, instead of raising an error by calling an inexistent procedure you could signal your custom error message.

DELIMITER $$

CREATE TRIGGER DeviceCatalog_PreventDeletion
BEFORE DELETE ON DeviceCatalog
FOR EACH ROW
BEGIN

  IF old.id IN (1,2) THEN -- Will only abort deletion for specified IDs
    SIGNAL SQLSTATE '45000' -- "unhandled user-defined exception"
      -- Here comes your custom error message that will be returned by MySQL
      SET MESSAGE_TEXT = 'This record is sacred! You are not allowed to remove it!!';
  END IF;

END
$$

DELIMITER ;

The SQLSTATE 45000 was chosen as MySQL's Reference Manual suggests:

To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”

This way your custom message will be shown to the user whenever it tries to delete records ID 1 or 2. Also, if no records should be deleted from the table, you could just remove the IF .. THEN and END IF; lines. This would prevent ANY records from being deleted on the table.