0

I can't make this work. I want to create a trigger that deletes the child foreign keys before I delete the parent primary key. This is the code that I am using on phpmyadmin:

delimiter // 
CREATE TRIGGER removeforeign  
before delete 
on products
FOR EACH ROW 
BEGIN 
delete from sales_details
where product_code=21; 
END// 
delimiter ;

Right now, I can't make trigger works at all. Not for insert, delete, update. In this code, it will delete where product_code is 21 because it's only an attempt to get the syntax right, I am still to figure it out how to compare with what is being deleted. Please, help me with the syntax. I can't see why it isn't working.

0

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well.

ALTER TABLE sales_details
ADD FOREIGN KEY fk_product_code(product_code)
REFERENCES products(product_code)
ON DELETE CASCADE;

But if you'd like to use a trigger to delete rows from the products table that have the same product_code as the row deleted, this should do the trick:

 delimiter // 
 CREATE TRIGGER removeforeing 
  BEFORE DELETE ON products
  FOR EACH ROW BEGIN
    DELETE FROM sales_details WHERE product_code = OLD.product_code;
 END// 
 delimiter ;

Edit

Per documentation,

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

  • thanks for your answer. what is this OLD clause? what does it do? and unfortunately, I keep receiving "sintax error" on phpmyadmin. – guest21us Dec 16 '14 at 21:07
  • sounds good to me. I am still getting sintax errors anyway. I didn't know abou cascade yet. – guest21us Dec 16 '14 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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