Delete help

[MC]

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??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

MC (an*******@discussions.microsoft.com) writes:

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??

If you have foreign key constraints to the referring tables you can
do this:

DELETE Controller WHERE id = @id
IF @@error = 547
UPDATE Controller SET isactive = 0 WHERE Id = @id

However, there is no way to suppress the error message from the
constraint violation, so you will have to tame the client to swallow
them.

It is unfortunately so that 547 is a very generic message about
constraint violation, so it is not a good idea to have the client
to always drop 547 on the floor, since in many cases constraint
violations are due to bugs in the application.

As long as you are in SQL2000, I would go for the 14 NOT EXISTS clauses -
or always set isactive = 0. The next version of SQL Server which is
beta now, offers improved error handling.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Trociu]

On 2004-08-05, MC wrote:

...
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??

You can change your procedure that inserts ControlersID into SendControlers
tables (if only you can) in way that also updates Controlers table setting isActive to 1.
Then you do not have a problem while trying to delete specified controler.
DELETE FROM Controlers WHERE Id = @id AND isActive = 0;

Trociu
--
Plain-text over all!!!
/**********/
write me : trociu@autonom ict pwr wroc pl
search me : gg: 1382729

[Tony N.]

MC <an*******@discussions.microsoft.com> wrote in message news:<41**********************@news.newsgroups.ws> ...

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??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You can do it with a single NOT EXISTS

IF NOT EXISTS (
SELECT controllerID
FROM (
SELECT controllerID FROM tab1
UNION SELECT controllerID FROM tab2
UNION SELECT controllerID FROM tab3
) tab
WHERE controllerID = @controller_to_check
)

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Ii have 14 tables of SendController each for a certain purpose. <<
That is a <verb><object> name, not an entity/table name -- appropriate
for a procedure but not a table. Have you read ISO-11179 or a book on
data modeling? And these 14 tables are completely different from a
logical view (that also means different in structure -- read Date and
Mcgovern)? You cannot have a "purpose_code" in one table?
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 [sic] <<

Okay, you write code with assembly language style bit flags and don't
know that a row is not a record. I am now pretty sure, even without
DDL, that you have a serious design flaw.

It is a called splitting attributes; you have a single attribute and
instead of putting it into a column (columns are not fields, by the
way) you create an array of tables for each value. The result is
complex queries like this -- they are usually expensive queries and
lose data integrity.

Would you like to try again with specs? Ther are wasy to model a
class hierarchy in SQL, if that is what you are trying to do.