A trigger can it transform a column into counter ?

Hi with you all,
I need to automatically increase the value of a column by one. Except of cou
rse
for the first value of my column, in that case the value must be 1.
It's like a counter.
In the table this counter must be initialized by 1 for each new value of
"InheritedKey". The following line must have the value increase by one for e
ach
"Key" value increased.
Sample :
Key InheritedKey AutomaticValue
12345 40 1
12346 40 2
12347 41 1
12348 40 3
12347 41 2
Is it possible by a trigger ?
Thanks by advance for any help.
Thanks to have read until there.You could use an INSTEAD OF trigger...
Note, you need to use tablockx in the transaction below in order to make
sure you get the max(...)+1 as a unique value - if two sessions run the code
at exactly the same time they would get the same value and force a duplicate
key on insert.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"YDN" <fgargamel@.hotmail.com> wrote in message
news:%23TgEqCI1FHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi with you all,
> I need to automatically increase the value of a column by one. Except of
> course
> for the first value of my column, in that case the value must be 1.
> It's like a counter.
> In the table this counter must be initialized by 1 for each new value of
> "InheritedKey". The following line must have the value increase by one for
> each
> "Key" value increased.
> Sample :
> Key InheritedKey AutomaticValue
> 12345 40 1
> 12346 40 2
> 12347 41 1
> 12348 40 3
> 12347 41 2
> Is it possible by a trigger ?
> Thanks by advance for any help.
> Thanks to have read until there.
>|||As (Key, InheritedKey) isn't unique in your sample data it looks like
the "AutomaticValue" is redundant unless there are some other key
columns that you haven't specified. It does help if you post DDL,
including keys and constraints. Otherwise we can only guess at your
requirements.
If you don't have a key at all then I don't think this will be very
straightforward in a trigger - nor should it be necessary. More
important to fix the table design first.
David Portas
SQL Server MVP
--|||> Note, you need to use tablockx in the transaction below in order to make
> sure you get the max(...)+1 as a unique value - if two sessions run the
> code
Hmm, Tony ,should not be enough to use (updlock ,holdlock) to get a max
value
Can you elaborate a little bit?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u9VmUMI1FHA.2312@.TK2MSFTNGP14.phx.gbl...
> You could use an INSTEAD OF trigger...
> Note, you need to use tablockx in the transaction below in order to make
> sure you get the max(...)+1 as a unique value - if two sessions run the
> code at exactly the same time they would get the same value and force a
> duplicate key on insert.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key
> violation,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "YDN" <fgargamel@.hotmail.com> wrote in message
> news:%23TgEqCI1FHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||Hi Uri,
Yer, you are probably right on that one :)
But i'd use updatelockx rather than the holdlock, holdlock is a big cause of
deadlocking.
Anything that will correctly serialise the MAX, i even though of using the
serialisable transaction isolation level but i dont have a range predicate
so doubt it would work - need to test.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2323vdtJ1FHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hmm, Tony ,should not be enough to use (updlock ,holdlock) to get a max
> value
> Can you elaborate a little bit?
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:u9VmUMI1FHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Hi with you all, Hi and thank you David,
I'm Sorry I make a mistake in my sample
Sample :
Key InheritedKey AutomaticValue
12345 40 1
12346 40 2
12347 41 1
12348 40 3
12349 41 2
The table is the line of (something like) an invoice database. The key is th
e number of the line (unique excuse me) and the
InheritedKey is the Invoice number.
I want to automate the number of line of an invoice.
Thanks by advance for any help.
Thanks to have read until there.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le mess
age de news:
1129709255.420542.258460@.g43g2000cwa.googlegroups.com...
> As (Key, InheritedKey) isn't unique in your sample data it looks like
> the "AutomaticValue" is redundant unless there are some other key
> columns that you haven't specified. It does help if you post DDL,
> including keys and constraints. Otherwise we can only guess at your
> requirements.
> If you don't have a key at all then I don't think this will be very
> straightforward in a trigger - nor should it be necessary. More
> important to fix the table design first.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi with you all, Hi and thank you Tony and Uri,
I'm sorry my sample got a mistake :
Sample :
Key InheritedKey AutomaticValue
12345 40 1
12346 40 2
12347 41 1
12348 40 3
12349 41 2
I'm not sure to understand the Tony solution and the uri contribution ?
Well I'm not so far to think that an "INSTEAD OF trigger" is an humour tech
nics rather than an sql technics...
I'm sorry I'm a Newbie.
Is there an automatic solution to make a counter in my invoice line table.
Thanks by advance for any help.
Thanks to have read until there.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> a crit dans le message de news: OoP0myJ1FH
A.3600@.TK2MSFTNGP10.phx.gbl...
> Hi Uri,
> Yer, you are probably right on that one :)
> But i'd use updatelockx rather than the holdlock, holdlock is a big cause
of deadlocking.
> Anything that will correctly serialise the MAX, i even though of using the
serialisable transaction isolation level but i dont
> have a range predicate so doubt it would work - need to test.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%2323vdtJ1FHA.3300@.T
K2MSFTNGP15.phx.gbl...
>|||You will need to test this, but it would be something like...
create table testtrg (
mycol int not null ,
lineitem tinyint not null
)
go
insert testtrg ( mycol, lineitem ) values ( 1, 1 )
insert testtrg ( mycol, lineitem ) values ( 2, 1 )
insert testtrg ( mycol, lineitem ) values ( 3, 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
insert testtrg ( mycol, lineitem )
select mycol, isnull( ( select max( lineitem )
from testtrg t
where t.mycol = i.mycol ), 0 ) + 1
from inserted i
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg order by mycol
insert testtrg ( mycol, lineitem ) values( 1, 0 )
select * from testtrg order by mycol
insert testtrg ( mycol, lineitem ) values( 1, 0 )
select * from testtrg order by mycol
insert testtrg ( mycol, lineitem ) values( 2, 0 )
select * from testtrg order by mycol
go
drop table testtrg
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"YDN" <fgargamel@.hotmail.com> wrote in message
news:eq%23P9rL1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Hi with you all, Hi and thank you Tony and Uri,
> I'm sorry my sample got a mistake :
> Sample :
> Key InheritedKey AutomaticValue
> 12345 40 1
> 12346 40 2
> 12347 41 1
> 12348 40 3
> 12349 41 2
> I'm not sure to understand the Tony solution and the uri contribution ?
> Well I'm not so far to think that an "INSTEAD OF trigger" is an humour
> technics rather than an sql technics...
> I'm sorry I'm a Newbie.
> Is there an automatic solution to make a counter in my invoice line table.
> Thanks by advance for any help.
> Thanks to have read until there.
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> a crit dans le message de
> news: OoP0myJ1FHA.3600@.TK2MSFTNGP10.phx.gbl...
>|||Hi with you all, Hi and thank you Tony,
Well I'll test as soon as I have access to a Sql server.
I suppose I add as line like :
insert testtrg ( mycol, lineitem ) values ( 1, 1 )
insert testtrg ( mycol, lineitem ) values ( 2, 1 )
insert testtrg ( mycol, lineitem ) values ( 3, 1 )
as I consider it will have case ?
Thanks to have read until there.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> a crit dans le message de news: %23wfAkwL1
FHA.2964@.TK2MSFTNGP09.phx.gbl...
> You will need to test this, but it would be something like...
> create table testtrg (
> mycol int not null ,
> lineitem tinyint not null
> )
> go
> insert testtrg ( mycol, lineitem ) values ( 1, 1 )
> insert testtrg ( mycol, lineitem ) values ( 2, 1 )
> insert testtrg ( mycol, lineitem ) values ( 3, 1 )
> go
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
> insert testtrg ( mycol, lineitem )
> select mycol, isnull( ( select max( lineitem )
> from testtrg t
> where t.mycol = i.mycol ), 0 ) + 1
> from inserted i
> end
> go
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg order by mycol
> insert testtrg ( mycol, lineitem ) values( 1, 0 )
> select * from testtrg order by mycol
> insert testtrg ( mycol, lineitem ) values( 1, 0 )
> select * from testtrg order by mycol
> insert testtrg ( mycol, lineitem ) values( 2, 0 )
> select * from testtrg order by mycol
> go
> drop table testtrg
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "YDN" <fgargamel@.hotmail.com> wrote in message news:eq%23P9rL1FHA.3892@.TK2
MSFTNGP12.phx.gbl...
>