I have error "#1363 - There is no NEW row in on DELETE trigger "

Question

I have book and store_order tables.

I want to make trigger(but it contain error):

DELIMITER $$
    
CREATE TRIGGER t1 
BEFORE DELETE ON store_order
FOR EACH ROW 
BEGIN
    
    UPDATE book SET number = number + NEW.quantity WHERE ISBN = NEW.ISBN;
    
END
$$
    
DELIMITER ;

Answer 1

DELIMITER $$
CREATE
    TRIGGER t2 AFTER delete 
    ON library.store_order
    FOR EACH ROW BEGIN

        update library.book 
        set library.book.number = (library.book.number + OLD.quantity)
        where library.book.ISBN = OLD.ISBN;

    END$$
DELIMITER ;

Answer 2

The doc says below:

NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

So, NEW.col_name only works for INSERT or UPDATE as shown below:

CREATE TRIGGER my_trigger
AFTER INSERT ON test FOR EACH ROW
   -- ↑ Here
...

Or:

CREATE TRIGGER my_trigger
AFTER UPDATE ON test FOR EACH ROW
   -- ↑ Here
...

Answer 3

Use OLD instead of NEW when you want to get the deleted object.

for an example of my case. I'm getting the id of the newly added role by calling

NEW.id

and getting the same field's value while deleting by calling

OLD.id

Example:

DELIMITER $$
CREATE TRIGGER after_insert_role
AFTER INSERT ON role FOR EACH ROW
 BEGIN
   INSERT INTO `sync_mapping`
 (`operation_type`, `table_name`, `oid`, `end_point`) 
  VALUES
 ('insert', 'role', NEW.id, 'new/role');
END $$


 DELIMITER $$
  CREATE TRIGGER after_delete_role
  AFTER DELETE ON role FOR EACH ROW
   BEGIN
    INSERT INTO `sync_mapping` (`operation_type`, `table_name`, `oid`, `end_point`)        VALUES
  ('delete', 'role', OLD.id, 'delete/role');
END $$

Answer 4

Whenever we are deleting the data USE

OLD. instead of NEW.

CREATE TRIGGER user_history_delete AFTER DELETE ON user FOR EACH ROW INSERT INTO user_history(action , name ) VALUES ("Delete" , OLD.name );