16

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

Laurel
  • 5,965
  • 14
  • 31
  • 57
JatSing
  • 4,857
  • 16
  • 55
  • 65
  • NEW.fieldname exists when you insert or update a record; when you delete nothing new is coming to database!!! The error is clear: you can't use NEW.id because NEW is not permitted in a DELETE trigger – Marco Sep 29 '11 at 10:17
  • 1
    According to the linked article the select statatement should fail - your select statement reads like a real one and not like the `select into dummy` from the article which is intended to fail. – miherrma Sep 29 '11 at 12:24

4 Answers4

21

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.

mathielo
  • 6,725
  • 7
  • 50
  • 63
  • i can't get this to work with jdbc connector. It throws an error: createSQLException unknown table in a where clause. – user2782001 Oct 06 '18 at 22:38
  • Can you clarify the point about deleting no records from the table? If I run a single delete query for multiple rows, but only one row triggers the condition, will the other rows be deleted? – Stevoisiak May 18 '21 at 19:39
20

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 ;
Devart
  • 119,203
  • 23
  • 166
  • 186
  • 1
    MySQL has added the SIGNAL command to raise errors. I think it was added after this answer. If anyone is interested: http://dev.mysql.com/doc/refman/5.5/en/signal.html – Reactgular Feb 15 '15 at 23:17
  • Great addition @MathewFoscarini, I've just re-answered the question using Devart's answer and the SIGNAL command you suggested. – mathielo Apr 27 '15 at 17:16
6

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.

wonk0
  • 13,402
  • 1
  • 21
  • 15
2

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;
Mircea Soaica
  • 2,829
  • 1
  • 14
  • 25