删除记录时如何删除文件?

问题描述：

我有一张桌子:

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 that is referenced by it should be deleted as well. There are two scenarios when records are deleted from this table:

用户删除一张特定的照片.
用户删除一个特定的属性对象(例如在房地产属性"中)，并且所有引用该属性的照片将由ON DELETE CASCADE自动删除.

User deletes one particular photo.
User deletes one particular property object (as in "real estate property"), and all the photos referencing that property are deleted automatically by ON DELETE CASCADE.

我知道我可以在删除属性之前选择数据库中所有引用的照片，然后将它们连同文件一一删除，但是我正在寻找一种替代解决方案.

I know I can select all the referenced photos in the database before deleting a property and delete them along with their files one by one, but I'm looking for an alternate solution. Is it possible to catch the moment when a record in the photo table is deleted and delete the file automatically, without resigning the CASCADE clause, maybe in a trigger somehow?

答

您正在寻找删除触发器.请参阅此处，了解类似的问题和&解决方案.可以通过安装 sys_exec 来实现外部操作.

You are looking for a DELETE TRIGGER. See here for a similar Problem & Solution. External action can be achieved via installation of sys_exec.

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

删除记录时如何删除文件?

相关推荐