Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I would like to delete data in multiple tables, from a trigger but it seem like my syntax is incorrect.

I got the error: "you've got a error in your MySQL syntax please check the manual".

This is the SQL for the trigger: 

CREATE TRIGGER `delete_organisation`
BEFORE DELETE ON `organisations`
FOR EACH ROW
DELETE FROM contacts WHERE OLD.id = organisation_id ; 
DELETE FROM orders WHERE OLD.id = orders.organisation_id;
DELETE from organisations_products where OLD.id = organisation_id ;
DELETE FROM organisations WHERE OLD.id = organisations.parent ;

Any idea what I'm doing wrong?

share|improve this question
3  
Why not use a foreign key with on delete cascade? – a_horse_with_no_name Aug 27 '12 at 7:51
because there are trigger on all those other table , and on delete cascade , do not fire trigger on mysql – raoul Cu Aug 27 '12 at 13:47
Ah that stupid MySQL "feature" again. – a_horse_with_no_name Aug 27 '12 at 13:54

1 Answer

up vote 3 down vote

You're missing a BEGIN ... END, as follows:

CREATE TRIGGER `delete_organisation`
BEFORE DELETE ON `organisations`
FOR EACH ROW
BEGIN
  DELETE FROM contacts WHERE OLD.id = organisation_id ; 
  DELETE FROM orders WHERE OLD.id = orders.organisation_id;
  DELETE from organisations_products where OLD.id = organisation_id ;
  DELETE FROM organisations WHERE OLD.id = organisations.parent ; 
END $$
share|improve this answer
Thanks ! that was the problem ! – raoul Cu Aug 27 '12 at 13:47
DELIMITER $$ will be first line of above code. isn't it? – diEcho Jan 23 at 4:54
yes, indeed so. – Shlomi Noach Jan 23 at 11:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.