MySQL Trigger: Delete From Table AFTER DELETE

Question

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 2 patron_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 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 $$? — Ohgodwhy
– Ohgodwhy, Commented Aug 5, 2012 at 17:07
@Ohgodwhy: just a note about memcache question - it was linked only after I added it. It was no in "Related" before I did it. — zerkms
– zerkms, Commented May 13, 2014 at 2:16

P̲̳x͓L̳ · Accepted Answer · 2019-01-29 17:10:32Z

56

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.

edited Jan 29, 2019 at 17:10

P̲̳x͓L̳

3,6613 gold badges31 silver badges37 bronze badges

answered Aug 5, 2012 at 17:18

vivek_jonam

3,2978 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ohgodwhy Over a year ago

Maaaaaaaaaaaaaaaaaan. It all ended up being the semicolon in my WHERE clause. I'm such a fool. Thanks for that vivek, it's appreciated!

Air Over a year ago

Why use a compound statement block for just one statement? There's no requirement in the CREATE TRIGGER syntax to do so.

Ulterior Over a year ago

dont forget the ";" - should be in BOLD

Andrew G. · Accepted Answer · 2016-07-19 05:40:59Z

17

Why not set ON CASCADE DELETE on Foreign Key patron_info.pid?

answered Jul 19, 2016 at 5:40

Andrew G.

4394 silver badges11 bronze badges

2 Comments

muffir Over a year ago

If the table engine supports foreign keys this solution should be prefered before using trigger to do that.

Magnus Lind Oxlund Over a year ago

There are certain features that exclude the use of foreign key constraints. One of them is the CHECK constraint. So if a table has a CHECK constraint, it can't also have an ON CASCADE DELETE constraint.

Tamil Selvan C · Accepted Answer · 2015-11-19 15:58:11Z

0

create trigger doct_trigger
after delete on doctor
for each row
delete from patient where patient.PrimaryDoctor_SSN=doctor.SSN ;

edited Nov 19, 2015 at 15:58

Tamil Selvan C

20.3k12 gold badges55 silver badges72 bronze badges

answered Nov 19, 2015 at 14:38

alireza abedini

21

Collectives™ on Stack Overflow

MySQL Trigger: Delete From Table AFTER DELETE

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related