trigger increment by 1 - 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 2008 Forums

Transact-SQL (2008)

trigger increment by 1

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-10-07 : 02:12:59

hey i create trigger like this

CREATE TRIGGER [dim_geography_trigger]
ON dbo.dim_GEOGRAPHY
AFTER INSERT
AS

DECLARE @LW INT
SELECT @LW =MAX(GEOGRAPHY_sk)
FROM dbo.dim_GEOGRAPHY

UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1

this trigger will update all records but i want to update only last inserted record..how can i do this..

my table stucture

GEOGRAPHY_sk GEOGRAPHY_id country
10 111 india
11 190 japan
12 3 london

so if i insert any records into my table my GEOGRAPHY_sk will increment by 1..

anand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-07 : 02:18:14

why don't you just use identity column ?

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 02:38:13

Drop the entire trigger and just run this once, it will increment GEOGRAPHY_sk by 1 for each new record in the table:

ALTER TABLE dim_GEOGRAPHY ALTER COLUMN GEOGRAPHY_sk int NOT NULL IDENTITY(1, 1)

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-07 : 03:11:01

Lumbago,

I don't think you can alter a column to add in the identity property

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 03:20:39

Your'e right...you'd have to create a new column:

ALTER TABLE dim_GEOGRAPHY DROP COLUMN GEOGRAPHY_sk
ALTER TABLE dim_GEOGRAPHY ADD GEOGRAPHY_sk int NOT NULL IDENTITY(1, 1)

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 04:24:23

CREATE TRIGGER [dim_geography_trigger]
ON dbo.dim_GEOGRAPHY
AFTER INSERT
AS

select @id = coalesce(max(GEOGRAPHY_sk),0) from dim_geography
set rowcount 1
while exists (select * from dim_geography where GEOGRAPHY_sk is null)
begin
update dim_geography set GEOGRAPHY_sk = @id+1, @id = @id+1 where GEOGRAPHY_sk is null
end
set rowcount 0
go

The identity would be easier but you would need to allow for gaps (or wait for sequences in Denali).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-10-07 : 06:53:40

ya i got this answer..this trigger work fine..

thanks

CREATE TRIGGER [LW_NO] ON dbo.dim_GEOGRAPHY
AFTER INSERT
AS
DECLARE @LW INT
SELECT @LW =MAX(GEOGRAPHY_sk)
FROM dbo.dim_GEOGRAPHY
UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1
from dim_geography
where geography_sk is null

anand

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-07 : 12:27:24

If you are oly adding a single row at a time, the trigger will work. If you ever add multiple rows, it will generate duplicate values. Also, you should place an explicit transaction around the SELECT/UPDATE pair to prevent two simultaneous inserts from generating duplicates.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources