gascon95 gascon95 -4 years ago 130
MySQL Question

MySQL select statement result in after delete trigger always null

I'm having a little issue with a trigger in a MySQL database. I have a DB with two tables: "tasks" and "files". The "tasks" table have a field which is a foreign key of the primary key from the "files" table. It also sometimes may be null.

What I'm trying to acomplish is to delete in the first place a row in the "tasks" table, and after that delete the corresponding row in the "files" table using a trigger.

This is the trigger I'm using right now:

DELIMITER //
CREATE TRIGGER after_delete_file AFTER DELETE ON tasks
FOR EACH ROW
BEGIN
DECLARE fileId int;
SELECT file INTO fileId FROM tasks WHERE id=old.id;
DELETE FROM files WHERE id=fileId;
END;//
DELIMITER ;


The field "file" in the "tasks" table is the one containing the foreign key. In the examples I've been running, that field has never been null.

The problem is that the select statement always returns null. The delete statement that triggers this trigger goes fine, but the row in the "files" table is never deleted. I've tried to insert the "fieldId" variable on a testing table, and it's always saving a null value.

Is there any problem on that trigger? Maybe I'm trying to do something merely impossible?

All the help is much appreciated :)

Answer Source

Since it should be looping over each deleted row, why would this not work?

DELIMITER //
CREATE TRIGGER after_delete_file AFTER DELETE ON tasks
FOR EACH ROW
BEGIN
    DELETE FROM files WHERE id=old.file;
END;//
DELIMITER ;

If that doesn't work, could try this:

DELIMITER //
CREATE TRIGGER after_delete_file AFTER DELETE ON tasks
FOR EACH ROW
BEGIN
    DELETE FROM files INNER JOIN tasks ON files.id=tasks.file WHERE tasks.id=old.id;
END;//
DELIMITER ;

but I don't think that should be necessary.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download