Geek asks and answers

How to delete files when deleting a record?

I have a table:

CREATE TABLE photo (
    photo_id BIGINT NOT NULL AUTO_INCREMENT,
    property_id BIGINT NOT NULL,
    filename VARCHAR (50) NOT NULL;
    ...
    PRIMARY KEY (photo_id),
    CONSTRAINT photo_fk_property FOREIGN KEY (property_id)
        REFERENCES property (property_id)
        ON DELETE CASCADE
);

When a row from this table is deleted, the file it refers to must also be deleted. There are two scenarios when records are deleted from this table:

  • The user deletes one specific photo.
  • The user deletes one specific property object (as in "real estate"), and all photos that reference this property are automatically deleted ON DELETE CASCADE.

I know that I can select all the reference photos in the database before deleting the property and delete them together with my files one by one, but I'm looking for an alternative solution. Is it possible to catch the moment when the record in the table is photodeleted and automatically delete the file without canceling the sentence CASCADE, possibly in the trigger?

+5
1

DELETE TRIGGER. . . sys_exec.

CREATE TRIGGER foobar
AFTER DELETE ON photo
FOR EACH ROW
BEGIN
   CALL sys_exec(concat('/bin/rm -f ',filename));
END
+8

More articles:


All Articles