Using Transactions and Identity Fields

Description

This example is given for SQL Server.

One of the common things done in Database Stored Procedures is to create a stored procedure which inserts data into more than one table. If the insertion has an identity field that needs to be used in another table - this could get complex. We will show how to do this with a simple example;

Example

In our example we will consider a simple User Management System that has Users and Groups (Users can belong to multiple groups, groups can have multiple users). This is accomplished with three basic tables with the table scripts given below in the reference materials section. All three tables have an identity field as their primary key - however both identity fields UserID and UserGroupID are Foreign Key's in the UserGroupMap table.

To accomplish inserting data into all three tables correctly we must use TRANS (transaction) and the SCOPE_IDENTITY() function. Here are the steps;

1. Start the transaction using the BEGIN TRAN

2. Insert data into the user table.

3. After the insertion store the identity value using SET @UserID = SCOPE_IDENTITY()

4. Insert data into the Group table

5. After the insertion store this identity value using SET @GroupID = SCOPE_IDENTITY()

6. With these values stored we can now insert into the n:n map table

7. Complete (commit) the transaction COMMIT TRAN

Here is a sample code for this

First create a start of transaction  (BEGIN TRAN)
Once you've inserted data that has an identity you can get that number by using @VariableName = SCOPE_IDENTITY() function - it will return the identity of the previous table insertion
You can now use that value in the next table, 
when you are complete - finish the transaction (COMMIT TRAN)

Here is an example that follows this

CREATE PROCEDURE [dbo].[sp_CreateUser]
    @UserName nvarchar(63),
    @UserPassword nvarchar(63),
    @UserEmail nvarchar(63)
AS 
    BEGIN TRY
        BEGIN TRAN
        
        DECLARE @userid AS INT
        DECLARE @groupid AS INT

        INSERT  INTO [User]
                (
                  [UserName],
                  [UserPassword],
                  [UserEmail],
                  [UserCreatedDate],
                  [UserLastLoginDate],
                  [UserIsDeletedBit]
	          )
        VALUES  (
                  @UserName,
                  @UserPassword,
                  @UserEmail,
                  GETDATE(),
                  NULL,
                  0 
                ) 
             
        SET @userid = SCOPE_IDENTITY()     
             
        INSERT  INTO [dbo].[UserGroup]
                (
                  [GroupName],
                  [GroupOwnerID],
                  [GroupIsDeletedBit],
                  [GroupIsOwnersPrimaryGroupBit],
                  [GroupDescriptionText]
	          )
        VALUES  (
                  @UserName + '''s group',
                  @userid,
                  0,
                  1,
                  'This is the primary group of ' + @UserName 
                ) 
	       
        SET @groupid = SCOPE_IDENTITY()   

        INSERT  INTO [dbo].[UserGroupMap]
                (
                  [UserID],
                  [UserGroupID]
	           )
        VALUES  ( @userid, @groupid ) 

        SET @groupid = (SELECT groupid FROM [dbo].[UserGroup] WHERE Groupname = 'Public')

        INSERT  INTO [dbo].[UserGroupMap]
                (
                  [UserID],
                  [UserGroupID]
	           )
        VALUES  ( @userid, @groupid ) 

	               
	

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT  ERROR_NUMBER() ErrorNumber,
                ERROR_MESSAGE() [Message]
    END CATCH

	RETURN @userid

Reference Materials

CREATE TABLE [dbo].[User](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](63)  NOT NULL,
	[UserPassword] [nvarchar](63)  NOT NULL,
	[UserEmail] [nvarchar](127)  NOT NULL,
	[UserCreatedDate] [datetime] NOT NULL,
	[UserLastLoginDate] [datetime] NULL,
	[UserIsDeletedBit] [bit] NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserGroup](
	[GroupID] [int] IDENTITY(1,1) NOT NULL,
	[GroupName] [nvarchar](255)  NOT NULL,
	[GroupOwnerID] [int] NULL,
	[GroupIsDeletedBit] [bit] NOT NULL,
	[GroupIsOwnersPrimaryGroupBit] [bit] NOT NULL,
	[GroupDescriptionText] [nvarchar](500)  NULL,
 CONSTRAINT [PK_User_RoleList] PRIMARY KEY CLUSTERED 
(
	[GroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserGroupMap](
	[UserID] [int] NOT NULL,
	[UserGroupID] [int] NOT NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserGroupMap]  WITH CHECK ADD  CONSTRAINT [FK_User_Roles_User_RoleList] FOREIGN KEY([UserGroupID])
REFERENCES [dbo].[UserGroup] ([GroupID])
GO
ALTER TABLE [dbo].[UserGroupMap] CHECK CONSTRAINT [FK_User_Roles_User_RoleList]
GO
ALTER TABLE [dbo].[UserGroupMap]  WITH CHECK ADD  CONSTRAINT [FK_User_Roles_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[UserGroupMap] CHECK CONSTRAINT [FK_User_Roles_Users]

Additional Information

http://msdn.microsoft.com/en-us/library/ms190315.aspx - MSDN Article on USing SCOPE_IDENTITY() function.

http://msdn.microsoft.com/en-us/library/aa237098(SQL.80).aspx - MSDN Article on managing Identities.

CET 4429 Lectures | Front Page