Creating TRIGGER need to autoincrement PK

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

General SQL Server Forums

New to SQL Server Programming

Creating TRIGGER need to autoincrement PK

Author

Topic

d247
Starting Member

6 Posts

Posted - 2014-04-06 : 18:35:38

Hi Everyone,
I am creating a trigger in SQL Server that will activate when my Athelete table has something "Inserted"

So far it works except for the fact that it won't insert a null into the Equipment_ID field. I need it to autoincrement, or do the last number + 1...

CREATE TRIGGER TRG_NEW_EQUIPMENT1
ON ATHLETE AFTER INSERT
AS
BEGIN
INSERT INTO Equipment Equipment_ID, Equipment_Model, Equipment_Year, Equipment_Brand, Equipment_Color, Equipment_Condition_Rating)
VALUES ('Big Spin','2016','K2','Blue','5')
END;
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-06 : 18:40:34

why don't you make Equipment_ID an identity field ?

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

d247
Starting Member

6 Posts

Posted - 2014-04-06 : 18:47:56

quote:
Originally posted by khtan

why don't you make Equipment_ID an identity field ?

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

How? I'm sorry, I have very limited knowledge of SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-06 : 19:43:01

an column with IDENTITY property will auto increment its value when a new record is inserted.

refer http://msdn.microsoft.com/en-us/library/ms186775.aspx

when you create the table, specify the identity column


create table table_name
(
    column_name  int identity
)

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

d247
Starting Member

6 Posts

Posted - 2014-04-07 : 18:45:05

quote:
Originally posted by khtan

an column with IDENTITY property will auto increment its value when a new record is inserted.

refer http://msdn.microsoft.com/en-us/library/ms186775.aspx

when you create the table, specify the identity column

create table table_name
(
column_name int identity
)

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

But I am not creating a new table, I am inserting a record into an exciting table, and I want it to use the next available primary key.. please see the code I originally posted.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-07 : 21:10:04

if you are able to change your table to use identity, it will saved you the trouble to increment the Equipment_ID

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-04-07 : 21:40:45

Or check out SEQUENCE which are unrelated to the structure of a particular table.

Do *NOT* do last_value + 1 . It will not always work in a multi-user situation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources