Solved

Multitable delete with TRIGGER.

Posted on 2014-11-23
2
110 Views
Last Modified: 2014-11-23
Neilsr helped me get started with my first stored procedure. Here is what I have now after rewriting his example for my database. Below is an abbreviated version of a 13 table delete. When I try to import it into phpmysql I get a 1064 syntact error after BEGIN. Every table involved including gallery has a galid.

I have tried:
WHERE tags.galid = OLD.id
WHERE tags.galid = OLD.galid

Maybe the problem is else where. Can someone help? Thanks.

DELIMITER $$
DROP TRIGGER IF EXISTS gal_delete;

CREATE TRIGGER gal_delete AFTER DELETE on gallery
FOR EACH ROW
BEGIN

DELETE FROM tags
    WHERE galid = OLD.id;

DELETE FROM comments
    WHERE galid = OLD.id;

DELETE FROM log
    WHERE galid = OLD.id;

END$$

Open in new window

0
Comment
Question by:kadin
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40461313
Because you have changed the delimiter you need to use $$ after your DROP statement:
DELIMITER $$
DROP TRIGGER IF EXISTS gal_delete$$

CREATE TRIGGER gal_delete AFTER DELETE on gallery
FOR EACH ROW
BEGIN

DELETE FROM tags
    WHERE galid = OLD.id;

DELETE FROM comments
    WHERE galid = OLD.id;

DELETE FROM log
    WHERE galid = OLD.id;

END$$

Open in new window

However, I cannot help but notice that in your case a FOREIGN KEY with the CASCADE DELETE option could be a better approach.    
What you can do is create the foreign keys in each of your child tables this way:
ALTER TABLE tags
    ADD FOREIGN KEY
    fk_tags_galid (galid)
    REFERENCES gallery (galid)
    ON DELETE CASCADE
    ON UPDATE CASCADE 

Open in new window

(I have added ON UPDATE CASCADE, meaning that when you update the galid in the master table it will be automatically updated in the child. You can remove this clause if your business rules are different)
0
 

Author Comment

by:kadin
ID: 40461464
That was it. Thanks for your help. I will think about your foriegn key suggestion.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question