Question 19991 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I make the following function called "animal_delete_func()" put the deleted animal entry into the deleted_animals table? – Mysql

Here is the code:


CREATE FUNCTION animal_delete_func()
RETURNS TRIGGER
BEGIN 
  -- Delete the feeding schedule entries for the animal

  DELETE FROM feeding_schedule WHERE animal_id = OLD.animal_id;
  
  -- Delete the food entries for the animal

  DELETE FROM food WHERE animal_id = OLD.animal_id;
 
 -- INSERT into deleted_animals table

    INSERT INTO deleted_animals (animal_id, animal_name, date_deleted)
    VALUES (OLD.animal_id, (SELECT animal_name FROM animals WHERE animal_id = OLD.animal_id), NOW());
 
 RETURN OLD


END$$

-- Set delimiter back to ;
DELIMITER ;

-- trigger that calls animal_delete_func() everytime an animal is deleted from the animals table

CREATE TRIGGER animal_delete_trigger
AFTER DELETE ON animals
FOR EACH ROW
CALL animal_delete_func();

I was expecting that the function would put the deleted animal entry into the deleted animals table, but it doesn’t work.

Tags:

2

Answers


  1. 0

    MySQL doesn’t have RETURN TRIGGER like PostGres does. Use an ordinary stored procedure, and pass the animal ID as a parameter.

    The SELECT subquery that gets the animal name can’t work, since the trigger runs after that row has been deleted. So pass OLD.animal_name as a parameter.

    DELIMITER $$

CREATE PROCEDURE animal_delete_func(old_animal_id, old_animal_name)
BEGIN 
    -- Delete the feeding schedule entries for the animal

    DELETE FROM feeding_schedule WHERE animal_id = old_animal_id;

    -- Delete the food entries for the animal

    DELETE FROM food WHERE animal_id = old_animal_id;

    -- INSERT into deleted_animals table

    INSERT INTO deleted_animals (animal_id, animal_name, date_deleted)
    VALUES (old_animal_id, old_animal_name, NOW());
 
 RETURN OLD


END$$

-- Set delimiter back to ;
DELIMITER ;

-- trigger that calls animal_delete_func() everytime an animal is deleted from the animals table

CREATE TRIGGER animal_delete_trigger
AFTER DELETE ON animals
FOR EACH ROW
CALL animal_delete_func(OLD.animal_id, OLD.animal_name);
    Login or Signup to reply.
  2. 0

    You are using a stored procedure syntax instead of a trigger syntax:
    Try this:

    DELIMITER $$

CREATE TRIGGER animal_delete_trigger
BEFORE DELETE ON animals
FOR EACH ROW
BEGIN
  -- Delete the feeding schedule entries for the animal
  DELETE FROM feeding_schedule WHERE animal_id = OLD.animal_id;
  
  -- Delete the food entries for the animal
  DELETE FROM food WHERE animal_id = OLD.animal_id;
  
  -- INSERT into deleted_animals table
  INSERT INTO deleted_animals (animal_id, animal_name, date_deleted)
  VALUES (OLD.animal_id, OLD.animal_name, NOW());
  
END$$

DELIMITER ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search