SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 insert incrementing ID values with trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrBloom
Starting Member

United Kingdom
34 Posts

Posted - 01/10/2014 :  10:57:30  Show Profile  Reply with Quote


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

USA
37157 Posts

Posted - 01/10/2014 :  12:22:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/10/2014 :  13:54:11  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
412 Posts

Posted - 01/10/2014 :  15:46:04  Show Profile  Reply with Quote
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

Go to Top of Page

MrBloom
Starting Member

United Kingdom
34 Posts

Posted - 01/11/2014 :  14:39:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/12/2014 :  02:00:58  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000