October 28, 2011 at 6:18 am
Hello,
I have to implement an active/inactive field on a table as we cannot delete the records. So putting this Boolean field will allow us after to filter this customer in our application (only active , inactive or all if we want to reactivate it latter).
I can do it in my java code but I was wondering if there is a way to use the check constraint to avoid to update this value (put it to false as inactive ) if the customer is used in another table .
Can we use queries in CHECK constraint (I did not find any close example to my problem) ? (I need to check another table to check this)
thanks a lot for your help.
October 28, 2011 at 6:55 am
I'm not sure what you want here. So I'll just guess and look for clarification.
Yes, you can use a bit data type in SQL Server. This will allow 1/0 to be read as active/inactive in your case.
From what I can understand, you might be looking for a trigger instead of a check constraint.
A check constraint only verifies data before being inserted. For example, check to make sure only positive numbers are entered.
A table trigger fires when a specified DML statement is executed (insert, update, or delete) against the table. In this case, if in a secondary table a customer becomes active, you can modify the primary customer table and update the customer's status through use of the trigger.
Again, that's me taking a guess at trying to understand your issue.
Look into table triggers in BOL and get back to us with some clarification. Perhaps a table definition or two.
October 28, 2011 at 7:03 am
It's technically possible, you can create a UDF that executes a query against a different table, and then call that UDF in your CHECK constraint. This MSDN page on CHECK constraints includes an example of a constraint that references a UDF: http://msdn.microsoft.com/en-us/library/ms188258.aspx. However, it does sound like you may be looking for a trigger, not a constraint. Can you provide more details on what the desired behavior is?
October 28, 2011 at 7:05 am
thanks for the info.
Yes I was wondering if I can use Sql Code in a CHECK Constraint the same way as in a trigger .
Thanks !.
October 28, 2011 at 7:15 am
Thanks Jon fox,
I saw this page but I wanted to avoid to create a UDF .
Sorry it was more by curiosity and for my knowledge I wanted to knew if I could use a query directly in a Check constraint
(in my case if a user want to inactivate a customer, put the active value to false , I wanted to check before that this customer was not used in others tables to to a "select count (customeroid )... .
thanks for the answers.
October 28, 2011 at 11:35 am
You can't just have select statement in a check constraint. For the purposes of what you are trying to accomplish a check constraint is just not the right approach. You want a delete trigger. In your case you want to NOT delete the record in some cases and instead mark it as inactive.
Keep in mind that check constraints are not validated on delete. Here is the same link Jon posted above for reference.
http://msdn.microsoft.com/en-us/library/ms188258.aspx
And just for the record a bit is NOT a boolean. It has 3 possible values (0, 1, null). Something to keep in mind when writing your trigger if your table allows nulls for your Active bit column. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2011 at 12:51 pm
Sean Lange (10/28/2011)
And just for the record a bit is NOT a boolean. It has 3 possible values (0, 1, null). Something to keep in mind when writing your trigger if your table allows nulls for your Active bit column. 😉
RABBLE RABBLE RABBLE!!!
You had to go there, eh?
But yeah, I agree. That's a good reminder.
I've had long discussions on how you can have an IsActive BIT NULL field and what NULL means in that sense.
October 28, 2011 at 1:48 pm
calvo (10/28/2011)
Sean Lange (10/28/2011)
And just for the record a bit is NOT a boolean. It has 3 possible values (0, 1, null). Something to keep in mind when writing your trigger if your table allows nulls for your Active bit column. 😉
RABBLE RABBLE RABBLE!!!
You had to go there, eh?
But yeah, I agree. That's a good reminder.
I've had long discussions on how you can have an IsActive BIT NULL field and what NULL means in that sense.
LOL. I figured somebody had to go there since the OP talked about his boolean column. It is surprising how many people don't realize how it works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply