title: Using Scope_Identity() to add 2 foreign keys in SQL Server
body: <p>I have a question about <code>Scope_Identity()</code> and transactions.</p>
  
  <p>I have these three tables and the PK in <code>MemberDetails</code> is the FK in the other two tables.</p>
  
  <p>I have been trying to create the stored procedure with <code>Scope_Identity()</code> so that the FK are automatically added in the tables, but I can't figure how to do it.</p>
  
  <p>Do you have any idea?</p>
  
  <p>Thanks in advance</p>
  
  <pre><code>CREATE TABLE [MarketingTarget] 
  (
      [MTid] int identity (5000,1) NOT NULL UNIQUE,
      [MDOB] date NOT NULL,
      [MSex] char (1) NOT NULL CHECK (MSex IN ('M','F')) DEFAULT 'M',
      [MemberID] int NULL,
      [MTUpdate] Date Null DEFAULT Getdate(),
      PRIMARY KEY ([MTid]),
      FOREIGN KEY (MemberID) REFERENCES [MemberDetails] ([MemberID]) 
              ON DELETE SET NULL 
              ON UPDATE CASCADE
  );
  
  CREATE TABLE [MembershipDetails] 
  (
      [MDid] int identity (2000,1) NOT NULL UNIQUE,
      [MType] varchar(10) NOT NULL CHECK (MType IN ('Monthly','Quaterly','Yearly'))  DEFAULT 'Monthly',
      [JoinDate] Date NOT NULL DEFAULT GETDATE(),
      [ExpiryDate] Date NULL,
      [MsUpdate] Date DEFAULT GETDATE(),
      [MemberID] int NULL,
      PRIMARY KEY ([MDid]),
      FOREIGN KEY (MemberID) REFERENCES [MemberDetails] ([MemberID]) 
              ON DELETE SET NULL
              ON UPDATE CASCADE  
  );
  
  CREATE TABLE [MemberDetails] 
  (
      [MemberID] int identity (1000,1) NOT NULL UNIQUE,
      [MName] varchar(100) NOT NULL,
      [MSurname] varchar(100) NOT NULL,
      [MPhone] varchar(20) NOT NULL UNIQUE,
      [MEmail] varchar(200) NOT NULL UNIQUE,
      [MAddress] varchar(250) NOT NULL,
      [MActive] char (1) NOT NULL CHECK (MActive IN ('Y','N')) DEFAULT 'Y',
      [MUpdateDate] Date NOT NULL DEFAULT GETDATE(),
      [MPhoto] Image NOT NULL
      PRIMARY KEY ([MemberID])
  );
  </code></pre>
  
  <pre><code>CREATE PROC spInsertMarketingTarget_test1
      @MDOB date,
      @MSex char (1),
      @MemberID int,
      @MTUpdate Date
  AS
      BEGIN TRY
          IF (SELECT COUNT(*) FROM MarketingTarget mt 
              WHERE MemberID = @MemberID) &gt; 0
              RAISERROR ('Fatal error. Member already exist.', 16, 1)
  
          BEGIN TRANSACTION
              INSERT INTO MarketingTarget
                  SELECT
                      @MDOB, @MSex, @MemberID, @MTUpdate
              COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
      SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_STATE() AS ErrorState,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_PROCEDURE() AS ErrorProcedure,
          ERROR_LINE() AS ErrorLine,
          ERROR_MESSAGE() AS ErrorMessage;
  
      -- Transaction uncommittable
      IF (XACT_STATE()) = -1
          ROLLBACK TRANSACTION
  
      -- Transaction committable
      IF (XACT_STATE()) = 1
          COMMIT TRANSACTION
  END CATCH;
  GO
  
  CREATE PROCEDURE spInsertMemberDetails
      @MemberID int = 0,
      @MName varchar(100),
      @MSurname varchar(100),
      @MPhone varchar(20),
      @MEmail varchar(200),
      @MAddress varchar(250),
      @MActive char (1),
      @MUpdateDate Date,
      @MPhoto image
  AS
  BEGIN
      SET NOCOUNT ON;
  
      INSERT INTO [MemberDetails] (MName, MSurname, MPhone, MEmail, MAddress, MActive, MUpdateDate, MPhoto)
      VALUES (@MName, @MSurname, @MPhone, @MEmail, @MAddress, @MActive, @MUpdateDate, @MPhoto)
  
      SET @MemberID = SCOPE_IDENTITY()
  END
  GO
  </code></pre>