MySQL Trigger: Delete From Table AFTER DELETE
Solution 1
I think there is an error in the trigger code. As you want to delete all rows with the deleted patron ID, you have to use old.id (Otherwise it would delete other IDs)
Try this as the new trigger:
CREATE TRIGGER log_patron_delete AFTER DELETE on patrons
FOR EACH ROW
BEGIN
DELETE FROM patron_info
WHERE patron_info.pid = old.id;
END
Dont forget the ";" on the delete query. Also if you are entering the TRIGGER code in the console window, make use of the delimiters also.
Solution 2
Why not set ON CASCADE DELETE on Foreign Key patron_info
.pid?
Related videos on Youtube
Ohgodwhy
Software Developer from California specializing in: PHP Laravel VueJS NodeJS Nginx Apache MySQL Amazon Web Services Debian/CentOS HTML/CSS JS (Frontend/Backend) I'm also the wearer of many, many hats.
Updated on July 15, 2022Comments
-
Ohgodwhy almost 2 years
Scope: Two tables. When a new patron is created, they have some information about them stored into a 2nd table (This was done using a trigger as well, it works as expected). Here's an example of my table structure and relationship.
Table 1-> patrons
+-----+---------+-----+ + id + name + val + +=====+=========+=====+ + 37 + george + x + +-----+---------+-----+ + 38 + sally + y + +-----+---------+-----+
Table 2 -> patron_info
+----+-----+----------+ + id + pid + name + +----+-----+----------+ + 1 + 37 + george + +----+-----+----------+ + 2 + 38 + sally + +----+-----+----------+
The administrator can manage the patrons. When they choose to remove a patron, the patron is removed from the table 1
patrons
. At this point, nothing happens to table 2patron_info
.I'm simply trying to create a trigger to delete from table 2, when table 1 has an item deleted. Here's what I've tried...
Initially, I try to drop the trigger if it exists (just to clear the air)...
DROP TRIGGER IF EXISTS log_patron_delete;
Then I try to create the trigger afterwards...
CREATE TRIGGER log_patron_delete AFTER DELETE on patrons FOR EACH ROW BEGIN DELETE FROM patron_info WHERE patron_info.pid = patrons.id END
At this point, I get a syntax error
1046: Check syntax near END on line 6
. I don't know what the error is at this point. I've tried several different variations. Also, am I required to use a delimiter here?Can anyone help restore my sanity?
-
vivek_jonam over 11 yearsWhat help would you need here? what is your real question?
-
Ohgodwhy over 11 years@vivek_jonam The delete trigger won't fire. It gives me a sytnax error on 'END' at line 6. Also, do I wrap this in a
DELIMETER $$
? -
zerkms almost 10 years@Ohgodwhy: just a note about memcache question - it was linked only after I added it. It was no in "Related" before I did it.
-
-
Ohgodwhy over 11 yearsMaaaaaaaaaaaaaaaaaan. It all ended up being the semicolon in my WHERE clause. I'm such a fool. Thanks for that vivek, it's appreciated!
-
Air almost 10 yearsWhy use a compound statement block for just one statement? There's no requirement in the
CREATE TRIGGER
syntax to do so. -
Ulterior over 9 yearsdont forget the ";" - should be in BOLD
-
muffir over 4 yearsIf the table engine supports foreign keys this solution should be prefered before using trigger to do that.