Pr99 Pr99 - 1 month ago 6
MySQL Question

How could I do this trigger delete with validations

I'm new to the forum, i have the following problem, I want after I delete a lease table locations I go in the table films and have the column situation becomes as available, but if I have the code of the movie in the table location in another register, I still leave the column state of the table film as leased, how would I do that?

that's what I've done

TRIGGER `tguDelete` AFTER DELETE ON `locations` FOR EACH ROW UPDATE movies SET situation = 'available' WHERE code_location = OLD.code_location


I wanted to post pictures of the tables, but I'm new to the forum
Please help me!

I use phpmyadmin, and I am new to mysql

Answer

I think your trigger definition required some work. For instance - you'd want to declare the "trigger function" which should execute AFTER the DELETE operations on locations table. This is how you can do it:

DROP TRIGGER IF EXISTS `tguDelete`;
DELIMITER $$
CREATE TRIGGER `tguDelete` AFTER DELETE ON `locations` 
FOR EACH ROW 
-- condition for rows for which the trigger would fire WHEN (OLD.code_location <> 0)
-- DECLARE
   -- if you had things you wanted to declare
BEGIN
   -- here you do the updating of the movies table:
   UPDATE movies SET situation = 'available' WHERE code_location = OLD.code_location;
END;$$
DELIMITER ;

If you don't need DELIMITERS (ie: PHPMyAdmin) then skip them such as

DROP TRIGGER IF EXISTS `tguDelete`;
CREATE TRIGGER `tguDelete` AFTER DELETE ON `locations` 
FOR EACH ROW 
-- condition for rows for which the trigger would fire WHEN (OLD.code_location <> 0)
-- DECLARE
   -- if you had things you wanted to declare
BEGIN
   -- here you do the updating of the movies table:
   UPDATE movies SET situation = 'available' WHERE code_location = OLD.code_location;
END;