trigger to delete child if parent deleted MySQL-CodePudding

hello fellows im having isues with this code, im using mysql workbench

I have this schema with 2 tables:

table 1: child

`id` ,  // primary key
`id_parent`, 
`name`

table 2: parent

`id`, // primary key
`name`

where I can use 1 parent row for many child rows, and child.id_parent=parent.id

how can i program a trigger that fires when i delete a parent row and deletes all of his child rows where child.id_parent=parent.id

I've been trying this but not succesfully when I try to delete a parent row

DELIMITER $$

CREATE TRIGGER parent_deleted
before DELETE
ON parent FOR EACH ROW
BEGIN
        delete from child where `child.id_parent`=`parent.id`;
END$$

DELIMITER ;

I tried changing the parent.id for old.parent_id, and tried after delete trigger too, nothing seems to work since i get this error:

DELETE FROM `prueba_luisdi`.`parent` 
WHERE (`id` = '9')

1054: Unknown column 'prueba_luisdi.parent.id' in 'where clause'

it has to be a trigger because if I use foreign key delete on cascade, then I cant register the child deleted on my log_changes table

CodePudding user response：

I agree with the comment above about using a cascading foreign key. But if you don't use foreign keys (many people do not) or don't use a storage engine that supports foreign keys, this might be an option.

If you want to reference the id column of the row being deleted in a trigger, use OLD.id.

https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html says:

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row.

This would look like this:

    delete from child where `child`.`id_parent` = OLD.`id`;

Also notice that if you use back-ticks to delimit qualified identifiers, each part is a separate identifier.