Trigger before delete MySql

Question

I have a table named user. This table has a foreign key to a department table. One user can be associated with one department. Before deleting a department, I would like to set any user (having that department ID) to a default value (1) to avoid a referential integrity error.

Do you know a good example. Most examples shows that the trigger is applied to one table. Here the trigger should be triggered on department but change values in user table.

Thanks.

Answer 1

I haven't tested it, but based on the documentation, this looks about right:

CREATE TRIGGER update_user_before_delete BEFORE DELETE ON department
  FOR EACH ROW BEGIN
    UPDATE user SET department = 1 WHERE department = OLD.department;
  END;

Answer 2

In most cases it is better to set the child value to NULL when the parent is deleted, rather than using a default of 1 like you are doing.

If you decide that this behavior is appropriate, then you can make it an attribute of the foreign key, and won't require a trigger at all.

Something like this:

ALTER TABLE `user`
ADD CONSTRAINT FK_USER_TO_DEPARTMENT FOREIGN KEY (department_id) 
REFERENCES `department` (department_id) ON DELETE SET NULL;