July 23, 2005 at 3:23 am
hi
i am having two columns in my table called customer name and allocated(having two values true and false).there are some customer names which are unassigned and corresponding the alloacated is false.what i want to do is that whenever the customer name is assigned the allocated value to automatically change to true.how do i do this.
July 23, 2005 at 8:56 am
I am not sure I understand your question but it seems to me that you're looking for a "computed column" - something that automatically defaults to true if customer name is assigned....
if so, the syntax would be something like this:
CREATE TABLE tblCustomerAllocation (CustomerName varchar(100),
Allocated AS CASE CustomerName
WHEN LEN(CustomerName) > 0 THEN 'True'
ELSE 'False'
END)
If this is not what you want please rephrase your question!
**ASCII stupid question, get a stupid ANSI !!!**
July 24, 2005 at 5:29 pm
I try to avoid computed columns..
I'd probably do this as follows:
create table foo_name
(
foo_name_id int identity(1,1) primary key,
foo varchar(100) default 'None',
[Name] varchar(50),
[allocated] bit default 0
)
go
create trigger tr_foo_name_alloc
on foo_name
after update, insert
as
If exists (select foo_name_id from inserted where [name] is not null and allocated = 0)
begin
print 'updating alloc value to 1, because name Name is not null'
update foo_name
set allocated = 1
from foo_name f
join inserted i
on f.foo_name_id = i.foo_name_id
where i.[Name] is not Null
and i.allocated = 0
end
else
begin
print 'Alloc 1 not triggered because of Name'
end
keeping in mind some of the performance issues associated with triggers..
--M Kulangara
Mathew J Kulangara
sqladventures.blogspot.com
July 25, 2005 at 6:09 am
Soumna,
The previous posts offer good solutions, but I read your question a little differently. You have the column named ALLOCATED and a process or condition called ASSIGNED.
From your question I infer that the assignment process is separate from providing a value to the CustomerName column. If this is the case, you would probably use Mathew's trigger on the table where the 'assignment' takes place or in the code that assigns the customer name.
HTH
-Chad
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply