August 5, 2004 at 2:41 am
i have table Controller (ControllerID, ControllerNAme, isActive) and tables SendControllerForCitizenClaim (SendID, ControllerID, Region), table SendControllerForCompanyClaim (SendID, ControllerID, REgion).. and so on .. i have 14 tables of SendController each for a certain purpose. I have a procedure for deleting controllers. I want that if a controller is sent (i.e its id is in at least one of the tables) then isActive=0 otherwise if the controller id isn't present in neither of the 14 tables then delete controler record. How do i do that wihout having to write 14 If Not exists statements?? |
August 5, 2004 at 4:45 am
The best method I can think of is as follows:
You need to build a reference table that holds the controller id's. First time arounbd will involve an insert statement from each of the tables.
After this, you can put triggers on the tables with controller id's and insert into the reference table if not exists.
To delete, you only need to check the one table.
This will give you a good long term plan with very little work once the original table has been built.
August 6, 2004 at 2:14 am
I'm a bit confused... If the controller is in at least one of the 14 tables, you want to set IsActive = 0; if it isn't, you want to delete the record from table Controller? That means every controller always has IsActive=0 ... why is this column there then? And why do you delete a controller any time he is not active... don't they have any holidays? I probably misunderstood something 🙂
OK, anyway, instead of 14 exists statements you could LEFT JOIN these 14 tables to the table Controller, and then use COALESCE in the WHERE condition (a, b, c ... are aliases of the Send tables):
WHERE COALESCE(a.SendId, b.SendId, c.SendId,.....,-1) = -1
This will return all the controllers that have no corresponding records in any of Send tables - of course, supposing that SendId is always > 0, which should be a rule for columns called SomethingId. (Oh, BTW, this is not the best database design - 14 identical tables. Much better would be 1 table SendController with column Claim_type, or something like that. Makes a lot easier to manipulate the data and make reports.)
HTH, Vladan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply