• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10079
  • Last Modified:

CREATE TRIGGER ... AFTER DELETE ON ... - MySQL

Hi, I am using two tables, lets call them biguser (90+ fields) and smalluser (10 fields). Smaller table is used to speed up search. I am trying to add a trigger that deletes record from smalluser after its deleted in biguser, just like this (id is autoincrement on biguser and non-autoincrement key field on smalluser):

DELIMITER |;
CREATE TRIGGER deluser AFTER DELETE ON biguser
FOR EACH ROW BEGIN
DELETE FROM smalluser WHERE id=OLD.id;
END;
|

And this query always return the following result:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; |' at line 4

MySQL Server is version 5.0.45. After insert trigger worked perfectly.
0
kichijai
Asked:
kichijai
  • 2
  • 2
2 Solutions
 
tigin44Commented:

You can not reference the OLD.id column in an AFTER DELETE trigger. Do this deleteion in a BEFORE DELETE trigger.
0
 
kichijaiAuthor Commented:
Same result for both AFTER DELETE and BEFORE DELETE
0
 
k_murli_krishnaCommented:
Take a look at example below from:
http://www.digitalpropulsion.org/Programming/Triggers_in_MySQL_5_0

DELIMITER $$

CREATE TRIGGER newsCategoryHandler
AFTER DELETE ON NewsCategories
FOR EACH ROW BEGIN
 DELETE FROM News WHERE catID=OLD.catID;
END;
$$

So, yours should work. There are few reasons for it not to:
1) Your table and column names are wrong.
2) Try a different delimiter like $$
3) See, that there is no loose character anywhere if you are executing from a file
4) It is a compile time error. Check semi-colon should not be there after start or end delimiter like in my example.
0
 
kichijaiAuthor Commented:
The following query worked:

DELIMITER $$
DROP TRIGGER IF EXISTS `deluser`$$
 
CREATE TRIGGER `deluser` BEFORE DELETE on `biguser`
FOR EACH ROW
BEGIN
DELETE FROM smalluser WHERE id=OLD.id;
END$$
 
DELIMITER ;

Open in new window

0
 
tigin44Commented:
you did change the trigger as I mentioned and it worked thats all. :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now