sequence and triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sequence and triggers

Author

Topic

lazydev
Starting Member

16 Posts

Posted - 2008-04-04 : 11:29:05

Hi SqlGurus,

I have created table T1 as
created table t1 (no int not null primary key,name varchar(30))

i have created a sequence for this table

when i give the following insert statement

insert into t1 (name) values ('xyz')

the next sequence val should be inserted in column no

this is done generally in oracle with before trigger

how about here .Can we do this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 11:31:48

you just want to define table like this

created table t1 (no int identity (start value,incremental value) not null primary key,name varchar(30))
for this to happen. Once you declare no as identity it will automatically increment value each time starting from startvalue incrementing by incremental value.

lazydev
Starting Member

16 Posts

Posted - 2008-04-04 : 11:33:57

quote:
Originally posted by visakh16

you just want to define table like this

created table t1 (no int identity (start value,incremental value) not null primary key,name varchar(30))
for this to happen. Once you declare no as identity it will automatically increment value each time starting from startvalue incrementing by incremental value.

I was particularly mentioned not to use identity for any of my tables .I should go with sequences and triggers .So i need help to do this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 11:46:37

[code]CREATE TRIGGER Trigger1
ON YourTable
INSTEAD OF INSERT
AS
BEGIN
DECLARE @MaxNo int
SELECT @MaxNo=MAX(no)
FROM YourTable

INSERT INTO YourTable
SELECT CASE WHEN @MaxNo IS NULL THEN StartValue
ELSE @MaxNo + incremental value
END,
name
FROM INSERTED
END[/code]

here startvalue is starting value of your sequence and incremental value the value to be incremented to get next sequential value.

lazydev
Starting Member

16 Posts

Posted - 2008-04-04 : 12:44:52

That's really great but each of my table contains nearly 30 columns.

and it's not necessary that i insert into all the columns every time

what about if my insert statement fails .
does it generates the unique sequence

Do we have an alternate at this time.

i use sqlserver2005 with sp2

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 12:48:37

quote:
Originally posted by lazydev

quote:
Originally posted by visakh16

you just want to define table like this

created table t1 (no int identity (start value,incremental value) not null primary key,name varchar(30))
for this to happen. Once you declare no as identity it will automatically increment value each time starting from startvalue incrementing by incremental value.

I was particularly mentioned not to use identity for any of my tables .I should go with sequences and triggers .So i need help to do this.

Any Particular reason why you don't want to use IDENTITY column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

lazydev
Starting Member

16 Posts

Posted - 2008-04-04 : 12:54:50

quote:
Originally posted by harsh_athalye

quote:
Originally posted by lazydev

quote:
Originally posted by visakh16

you just want to define table like this

created table t1 (no int identity (start value,incremental value) not null primary key,name varchar(30))
for this to happen. Once you declare no as identity it will automatically increment value each time starting from startvalue incrementing by incremental value.

it's just a standard mention from our client

I was particularly mentioned not to use identity for any of my tables .I should go with sequences and triggers .So i need help to do this.

Any Particular reason why you don't want to use IDENTITY column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 13:07:58

quote:
Originally posted by lazydev

That's really great but each of my table contains nearly 30 columns.

and it's not necessary that i insert into all the columns every time

what about if my insert statement fails .
does it generates the unique sequence

Do we have an alternate at this time.

i use sqlserver2005 with sp2

How do you think insert fails? any error? Then also it will take value correctly next time as its retrieving max value of no from your table each time. If you dont insert all values also no gets incremented correctly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources