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 ;

Trotta

If you want to access the data of the row which is to be deleted, you should use :old variable. Since there is no new row to be inserted (as it is a delete row trigger) you cannot use :new here. – Millhon

What does this have to do with CSS? – Fortalice

I do that before delete and i want to update in other table. – Trotta

possible duplicate of Delete row with a Mysql Trigger – Sheena

I only want to correct "update line" which problem on it :) – Trotta

I wander what is the problem in question to make it down vote ?! – Trotta

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 ;

Trotta

Answer 2

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 $$

Teleology

Answer 3

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 );

License

Answer 4

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

Conan