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 that is referenced by it should be deleted as well. There are two scenarios when records are deleted from this table:
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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With