kichijai
asked on
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.
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.
ASKER
Same result for both AFTER DELETE and BEFORE DELETE
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you did change the trigger as I mentioned and it worked thats all. :)
You can not reference the OLD.id column in an AFTER DELETE trigger. Do this deleteion in a BEFORE DELETE trigger.