advanced constraint

[influent]

I have a Depts table with two columns, and ID and a name. Here's a sample:

DeptID DeptName
12333 Office
12500 Warehouse
12501 Warehouse

I also have an Employees table, and each employee row has a DeptID column.
What I want to do is create a rule/constraint that will, for example, automatically convert the value of 12501 to 12500 for each employee in the Employees table (current and new). Then I can remove the 12501 row from the Depts table.

I hope I explained that well enough. It doesn't seem like a rule or constraint can actually do that, but maybe a trigger could, I have no experience with triggers.

 

[karluk]

I guess I don't see why you are looking at doing this through a trigger. Running a short script in query analyzer will do what you want.

update employee set deptid = 12500
where deptid = 12501
go
delete from department where deptid = 12501
go

 

[influent]

Thank you Karluk, but I'm aware that I can do that with a script. The table sample I gave is an extremely small portion of the actual table, so I'd have to do longer and more complex scripts. I don't want to have to go into Query Analyzer every time a new employee is added, I want it to be automatic. I think if I put the necessary script in an Update Trigger on the Employees table, that would do it. Do you know that it wouldn't?

 

[influent]

I meant to say an Insert Trigger. I will have to run the scripts in QA on the current records, but the trigger works on new records, I just tested it.