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

You can use any sql statement in your trigger code. When a record is deleted the trigger-code is fired. You can use the record wich fired the trigger (to select the department id) and then select any user with that id and update that user record. Good luck

Answer 3

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;