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