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.

The "trigger is applied to one table" Means a table is bound to a specific act on a specific table, it can't be bound (as in what causes it to run) to multiple tables. It can preform any actions the creator of the trigger has the rights to preform. — MindStalker
– MindStalker, Commented Dec 22, 2009 at 18:34
This SQL worked: CREATE TRIGGER dept_set_user_to_wuk BEFORE DELETE ON dept FOR EACH ROW UPDATE user SET deptid = 1 WHERE deptid = OLD.deptid; where dept is department — rtacconi
– rtacconi, Commented Jan 4, 2010 at 16:27

Andy West · Accepted Answer · 2009-12-22 18:10:28Z

11

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;

answered Dec 22, 2009 at 18:10

Andy West

12.5k4 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rtacconi Over a year ago

Iget: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE user SET user.deptID = 1 WHERE user.deptID = OLD.deptID' at line 1

Karl Bartel Over a year ago

You might need to change the delimiter to something else than ";" to create the trigger. Otherwise mysql tries to parse the statement only up to the first semicolon.

Ike Walker · Accepted Answer · 2009-12-22 18:32:45Z

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;

answered Dec 22, 2009 at 18:32

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

3 Comments

rtacconi Over a year ago

This makes sense but a requirement is that every user must belong to a department, or at least at department 1, which is the company. Yes it wuold be better and easier to use SET NULL but I cannot do that.

Behrouz.M Over a year ago

@rtacconi: you can use set default value in foreign key constraint

rtacconi Over a year ago

set default value in foreign key constraint to a specific number can break Mysql's backup when you restore/import your data, even if you disable the constraints check. I sounds crazy but it happened to me.

Stefan · Accepted Answer · 2009-12-22 18:10:53Z

0

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

answered Dec 22, 2009 at 18:10

Stefan

1571 silver badge11 bronze badges

Collectives™ on Stack Overflow

Trigger before delete MySql

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related