How to write a trigger to abort delete in MYSQL?

问题:

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

回答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 ;

回答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.

回答3:

Well, the error messages tells you quite clearly: in a DELETE trigger there is no NEW.

• In an INSERT trigger you can access the new values with NEW..
• In an UPDATE trigger you can access the new values with NEW., the old ones with - you guessed it - OLD.
• In a DELETE trigger you can acces the old values with OLD..

It simply makes no sense to have NEW in a DELETE, just as OLD in an INSERT makes no sense.

回答4:

As the error says: There is no NEW variable on delete.

you can use new.id only on insert and update. Use old.id instead.

SELECT * FROM DeviceCatalog WHERE DeviceCatalog.id=old.id;