column-defulat-value as auto-increment?

Thank you very much for the reply!
well, there is no way to update an identity column,
but is there a way to define a default value to a column
that makes auto-increment?
i tried in the SQL-Server-Enterprize-Manager\Design-Table
to set the default value of a column to MAX(recid_)+1...
any more ideas?
Thanks again,
edo.> well, there is no way to update an identity column,
> but is there a way to define a default value to a column
> that makes auto-increment?
> i tried in the SQL-Server-Enterprize-Manager\Design-Table
> to set the default value of a column to MAX(recid_)+1...
> any more ideas?
You could do this in a trigger, but maybe it might make more sense to
explain why you need this behavior.|||i made this trigger:
CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
FOR INSERT
AS
declare @.i1 int
declare c1 cursor for select Max(recid) from jobs
open c1
fetch next from c1 into @.i1
close c1
deallocate c1
update T1 set recid_=@.i1+1 where recid is NULL
do you think it's the most effecient way?
i doubt because i wonder first, wether i must open a
cursor, and second wether there is no direct way to
update the current inserted record, ruther than
search "where recid is NULL"
thank,
edo.
>--Original Message--
>> well, there is no way to update an identity column,
>> but is there a way to define a default value to a
column
>> that makes auto-increment?
>> i tried in the SQL-Server-Enterprize-Manager\Design-
Table
>> to set the default value of a column to MAX(recid_)
+1...
>> any more ideas?
>You could do this in a trigger, but maybe it might make
more sense to
>explain why you need this behavior.
>
>.
>|||You don't need a cursor:
SET @.i = (SELECT MAX(recid) FROM jobs)
Also, you use the INSERTED table to get the modified row(s).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"edo" <ewilde@.nana.co.il> wrote in message news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
> i made this trigger:
> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
> FOR INSERT
> AS
> declare @.i1 int
> declare c1 cursor for select Max(recid) from jobs
> open c1
> fetch next from c1 into @.i1
> close c1
> deallocate c1
> update T1 set recid_=@.i1+1 where recid is NULL
>
> do you think it's the most effecient way?
> i doubt because i wonder first, wether i must open a
> cursor, and second wether there is no direct way to
> update the current inserted record, ruther than
> search "where recid is NULL"
> thank,
> edo.
> >--Original Message--
> >> well, there is no way to update an identity column,
> >> but is there a way to define a default value to a
> column
> >> that makes auto-increment?
> >>
> >> i tried in the SQL-Server-Enterprize-Manager\Design-
> Table
> >> to set the default value of a column to MAX(recid_)
> +1...
> >> any more ideas?
> >
> >You could do this in a trigger, but maybe it might make
> more sense to
> >explain why you need this behavior.
> >
> >
> >.
> >|||Thanks for your helped,
i implemented your suggestion about the
SET @.i = (SELECT MAX(recid) FROM T1)
but i tried somthing like:
update inserted set recid=1
and got an error:
"the logical tables INSERTED and DELETED can not be
updated."
?
thanks agian,
edo.
>--Original Message--
>You don't need a cursor:
>SET @.i = (SELECT MAX(recid) FROM jobs)
>Also, you use the INSERTED table to get the modified row
(s).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"edo" <ewilde@.nana.co.il> wrote in message
news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
>> i made this trigger:
>> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
>> FOR INSERT
>> AS
>> declare @.i1 int
>> declare c1 cursor for select Max(recid) from jobs
>> open c1
>> fetch next from c1 into @.i1
>> close c1
>> deallocate c1
>> update T1 set recid_=@.i1+1 where recid is NULL
>>
>> do you think it's the most effecient way?
>> i doubt because i wonder first, wether i must open a
>> cursor, and second wether there is no direct way to
>> update the current inserted record, ruther than
>> search "where recid is NULL"
>> thank,
>> edo.
>> >--Original Message--
>> >> well, there is no way to update an identity column,
>> >> but is there a way to define a default value to a
>> column
>> >> that makes auto-increment?
>> >>
>> >> i tried in the SQL-Server-Enterprize-Manager\Design-
>> Table
>> >> to set the default value of a column to MAX(recid_)
>> +1...
>> >> any more ideas?
>> >
>> >You could do this in a trigger, but maybe it might
make
>> more sense to
>> >explain why you need this behavior.
>> >
>> >
>> >.
>> >
>
>.
>|||> update inserted set recid=1
You can't update the inserted / deleted tables.
Maybe you could show your table structure, sample data, and the results you
are trying to achieve, rather than have us reverse engineer your existing
trigger code. It might be that a trigger isn't even necessary for this, or
it might be that you could approach the trigger in a completely different
way. Your narrative a few posts back is difficult to follow, but might be
easier to understand if you show us your actual schema design. There might
be a much more efficient approach to whatever it is you mean by "cloning"...