The Passionate Craftsman

Ruby, PHP, MySql, Software engineering and more.

Monday, 4 January 2010

Create MySql Trigger before deleting a field

I asked some help from StackOverFlow and I had some partial help. I have a table with departments and the table user is referencing the department table, so every user belongs to one department (one to many). When I have to delete an old deparment it happens that there are some users associated to that department. Since a business rule is to have each user associated to one department or at least to department #1, which is the company it self, I want to set all users' department to 1, if the referenced department is deleted. This can be done with your programming language code or with a trigger, I decided to have a trigger (althogh I usually prefer to use the programming language and MySql as persistent layer, but this is another story).

I have used this code:

CREATE TRIGGER dept_set_user_to_wuk BEFORE DELETE ON dept
FOR EACH ROW UPDATE user SET deptid = 1 WHERE deptid = OLD.deptid;

I have created a trigger named dept_set_user_to_wuk saying that before deleting a row in dept (BEFORE DELETE ON dept), it should update each user's department to the default ID #1. It is worth to mention that the where clause is very important, since I used the OLD keyword. OLD keyword reference the field of the table specified after ON, in this case is the 'dept' table.

Labels: ,


Post a Comment

Subscribe to Post Comments [Atom]

<< Home