insert incrementing ID values with trigger

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)

insert incrementing ID values with trigger

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2014-01-10 : 10:57:30

Hi

I have an after insert trigger which, when a person ID is inserted into table1 the ID is inserted 3 times into table2.
What I would like to do is also insert 3 incrementing uniqueIDs into table2 in another column for the 3 inserts. These should start at the maximum value +1 of the the existing uniqueID. I don't want to use an identity column as the user could be deleting records and this would cause a problem. Here is my insert trigger.

any help would be appreciated




 
ALTER  TRIGGER [LAB].[trgInsertNewRecord] ON [LAB].[tblVolunteers]
FOR INSERT
AS

BEGIN 
INSERT INTO [LAB].[tblBiologicalSamplesLocation]
        (VolunteerID)
   
         SELECT VolunteerID  FROM inserted
END 

BEGIN 
INSERT INTO [LAB].[tblBiologicalSamplesLocation]
        (VolunteerID)
   
         SELECT VolunteerID  FROM inserted
END 

BEGIN 
INSERT INTO [LAB].[tblBiologicalSamplesLocation]
        (VolunteerID)
   
         SELECT VolunteerID  FROM inserted
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-10 : 12:22:52

I don't understand your concern about the identity column. Identity columns work fine with triggers provided you are using the proper function, for instance SCOPE_IDENTITY() for the insert of the original DML command.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-10 : 13:54:11

quote:
the user could be deleting records and this would cause a problem

It sounds like you are concerned about breaks in the sequence of the ID values. It is not a good practice to rely on an ID value having any meaning other than being a reference to rows in other tables. If you can tell us the problem it would cause we could suggest a solution that conforms to best practices.

Be One with the Optimizer
TG

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-10 : 15:46:04

Not sure which ID you want to increment, the table being inserted into or the source table, but maybe this??:



ALTER  TRIGGER [LAB].[trgInsertNewRecord] ON [LAB].[tblVolunteers]
FOR INSERT
AS
SET NOCOUNT ON

INSERT INTO [LAB].[tblBiologicalSamplesLocation]
        (VolunteerID)
   
         SELECT VolunteerID + (row_count - 1)  FROM inserted
         CROSS JOIN (SELECT 1 AS row_count UNION ALL SELECT 2 UNION ALL SELECT 3) AS row_counts

MrBloom
Starting Member

36 Posts

Posted - 2014-01-11 : 14:39:57

Thank you for your advice.

Scott I tried using these lines in the trigger. The id will increment by 3 but then it repeats itself ie 123 123 123 for each insert.

In any case I was thinking again and maybe I was trying to do something too complex for my needs. My ID column is a reference to another table. So I was thinking that maybe the IDENTITY column is not so bad after all and if I have to do a delete I can do a cascade delete , deleting the primary and foreign key in the other table. My only issue was that it would leave the id column non-sequential in parts if records are deleted but I guess this was just an issue of tidiness, the fact that id/ primary key columns are not sequential doesn't really matter I guess.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-12 : 02:00:58

quote:
Originally posted by MrBloom

Thank you for your advice.

Scott I tried using these lines in the trigger. The id will increment by 3 but then it repeats itself ie 123 123 123 for each insert.

In any case I was thinking again and maybe I was trying to do something too complex for my needs. My ID column is a reference to another table. So I was thinking that maybe the IDENTITY column is not so bad after all and if I have to do a delete I can do a cascade delete , deleting the primary and foreign key in the other table. My only issue was that it would leave the id column non-sequential in parts if records are deleted but I guess this was just an issue of tidiness, the fact that id/ primary key columns are not sequential doesn't really matter I guess.

Thanks

It doesnt matter if gaps are there in ID value as you can still retrieve them in order you want as values will still be in same sequence though not consecutive.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources