October 5, 2003 at 1:00 pm
hello,
is there any way to set the Default Value of a field to be other field value.
Thanks
October 5, 2003 at 2:18 pm
Not sure exactly what you're asking. Even though a column has a default, you can insert a different value (as long as its legal - no check constraint prohibiting it for example). Or are you wanting to actually change the default value?
Andy
October 6, 2003 at 8:25 am
soory what i mean is that i have a table with the follwoing cols:
AccId numeric
AccCustumId numeric
what i need is to define the Default value of AccCustumId to be AccID?
is that possiable.
Thanks
October 6, 2003 at 9:50 am
You could accomplish this by using a user-defined function (udf) as the default value. The udf would have to look up the most recently added value for the AccID field. If it is an identity column then you can try using IDENT_CURRENT() function to look up the most recently added identity for that table.
Here's an example script for a function (assuming the table's name is called ACCOUNT:
CREATE FUNCTION [dbo].[func_GetAccID] ()
RETURNS INT AS
BEGIN
RETURN IDENT_CURRENT('ACCOUNT')
END
If AccID is not an identity then you'll have to use SQL to lookup the value.
The other alternative to using a udf is to use a trigger. Instead Of Insert or After Insert could both work.
October 7, 2003 at 12:38 am
Hope this will solve your problem
Try this
CREATE TABLE mytable
(
AccId numeric IDENTITY(1,1),
AccCustumId as (AccId),
test int
)
insert into mytable (test) values (2)
select *from mytable
drop table mytable
October 7, 2003 at 12:45 am
quote:
Hope this will solve your problemTry this
CREATE TABLE mytable
(
AccId numeric IDENTITY(1,1),
AccCustumId as (AccId),
test int
)
insert into mytable (test) values (2)
select *from mytable
drop table mytable
I like that idea, but that makes column AccCustomId a "computed" column, which cannot be modified afterwards.
CVM.
October 7, 2003 at 1:29 am
hi!
if this cannot be achieved in another way, use the "old" approach, namely a trigger. for just inserts in your case you'd use something like that:
create trigger trig_name for insert on table_name as
update table_name set target_column = source_column
where table_id in (select table_id from inserted)
whereas
trig_name is the name of your trigger
table_name is the name of your table
target_column is the name of the column you want to have the same value as source_column
source_column is the name of the column that provides your value
table_id is the primary key field (or field combination) of your table
inserted is the pseudo table that contains a "shadow" of all inserted records by the statement that fired your trigger
best regards,
chris.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply