DefaultValue

Hi all,
I have a table like
Tablename: Test
tst_id uniqueidentifier Not null PK
= RowGuid
tst_ts2_id uniqueidentifier Not null
Default '?
tst_code Varchar 50 Not null
And Another table
Tablename: Test2
ts2_id uniqueidentifier Not null PK
ts2_name Varchar 50 Not null
ts2_default Boolean False
there is defined a FK tst_ts2_id and ts2_id
I would to insert into table Test a row with the folloing insert
INSERT INTO test
(tst_code)
VALUES ('123456')
Now I would, that there is a trigger, or something else, that inserts me an
tst_ts2_id the ID from Table Test2, where ts2_default = True.
How to du. I tryed with trigger, but it seams, that the trigger fires to
late, if I delete the FK, then it will be inserted.
I wrote a function that returns the Id to insert, but is it possible, that
like default-value can be insertted the result of a skalar function ?
Thank you all
Klaus AstnerI'm not certain if this is what you are trying to do and as you did not post
DDL or the statements or triggers that you tried I made some guesses, but
the following worked fine for me:
create table Test2 (
ts2_id uniqueidentifier Not null primary key default(newid()),
ts2_name Varchar(50) Not null,
ts2_default bit default(0)
)
go
create function dbo.def_ts2id() returns uniqueidentifier
as
begin
declare @.guid uniqueidentifier
select @.guid = ts2_id from test2 where ts2_default = 1
return @.guid
end
go
create table Test (
tst_id uniqueidentifier Not null primary key default(newid()),
tst_ts2_id uniqueidentifier Not null foreign key references test2(ts2_id)
default(dbo.def_ts2id()),
tst_code Varchar(50) Not null
)
go
insert into test2(ts2_name,ts2_default) values('default',1)
go
INSERT INTO test(tst_code) VALUES('123456')
go
select * from test
select * from test2
go
Result:
tst_id tst_ts2_id
tst_code
-- -- --
---
18CBDA2A-6B29-43C9-8A42-B86B9102E6D4 70DC14A0-F744-4006-8CE0-A9C8695D8E83
123456
ts2_id ts2_name
ts2_default
-- ---
-- --
70DC14A0-F744-4006-8CE0-A9C8695D8E83 default
1
Craig
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Astner Klaus" <k.astnerremove_this@.virgilio.it> wrote in message
news:eFHHSRPXDHA.2648@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I have a table like
> Tablename: Test
> tst_id uniqueidentifier Not null PK
> = RowGuid
> tst_ts2_id uniqueidentifier Not null
> Default '?
> tst_code Varchar 50 Not null
>
> And Another table
> Tablename: Test2
> ts2_id uniqueidentifier Not null PK
> ts2_name Varchar 50 Not null
> ts2_default Boolean False
>
> there is defined a FK tst_ts2_id and ts2_id
>
> I would to insert into table Test a row with the folloing insert
>
> INSERT INTO test
> (tst_code)
> VALUES ('123456')
> Now I would, that there is a trigger, or something else, that inserts me
an
> tst_ts2_id the ID from Table Test2, where ts2_default = True.
>
> How to du. I tryed with trigger, but it seams, that the trigger fires to
> late, if I delete the FK, then it will be inserted.
> I wrote a function that returns the Id to insert, but is it possible, that
> like default-value can be insertted the result of a skalar function ?
>
> Thank you all
>
> Klaus Astner
>
>
>

Default value of a GUID field?!

I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
2005 Express.
this column is the primary key and its default value is "newid()" in SQL
Server.
I have generated a DataSet in C# that has this column but its default value
is DBNull what can I do to make the default value be the return value of
this function ( or I want the Id be generated by default in the DataSet)?
System.Guid.NewGuid()
H? Leon
Have you cons?dered to create the guid from code and then insert it into the
table, in stead of letting the sql server generate it for you ?. Otherwise it
is possible to query the table for the inserted row, and reading the guid.
(if the guid isn't the primary key)
Hope this helps.
TIA
/Allan
"Leon_Amirreza" wrote:

> I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
> 2005 Express.
> this column is the primary key and its default value is "newid()" in SQL
> Server.
> I have generated a DataSet in C# that has this column but its default value
> is DBNull what can I do to make the default value be the return value of
> this function ( or I want the Id be generated by default in the DataSet)?
> System.Guid.NewGuid()
>
>
|||Thanks but this gets very tricky
and sorry for the clock problem
"Allan Bentsen" <Allan.Bentsen@.discussions.microsoft.com> wrote in message
news:6FDCD5BC-DC5F-4FC9-A4E4-D23E855F651F@.microsoft.com...[vbcol=seagreen]
> H? Leon
> Have you cons?dered to create the guid from code and then insert it into
> the
> table, in stead of letting the sql server generate it for you ?. Otherwise
> it
> is possible to query the table for the inserted row, and reading the guid.
> (if the guid isn't the primary key)
> Hope this helps.
> --
> TIA
> /Allan
>
> "Leon_Amirreza" wrote:
|||becuase I am using offline Typed DataSets and the GUID field is the Primary
Key of the table
"Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
news:uhK4HNz%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
> Thanks but this gets very tricky
> and sorry for the clock problem
> "Allan Bentsen" <Allan.Bentsen@.discussions.microsoft.com> wrote in message
> news:6FDCD5BC-DC5F-4FC9-A4E4-D23E855F651F@.microsoft.com...
>
|||Ouch!! That's not a good idea.
GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599
GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx
GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1
GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
news:O1BfFPz%23GHA.3352@.TK2MSFTNGP03.phx.gbl...
> becuase I am using offline Typed DataSets and the GUID field is the
> Primary Key of the table
> "Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
> news:uhK4HNz%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
>