MSSQL Insert

I am making a small sample database just to test some things out. I have two tables, Comment and CommentPrefix. Anytime a new comment is made, I also want to add a record to the commentPrefix table.

The code I have works fine, but it has one problem, if the second insert statement fails, for some reason the database gets messed up because the first insert still completes.

SO… I end up with a record in the comments table, but no record in the commentPrefix table because the second insert failed.

How do I write a procedure like this, so that it will update two tables, but I need to make sure that it works correctly… I need to make sure that if an error happens in either of the inserts… nothing gets saved…

(i also need similar for update and delete procedures but I will start with insert)…


ALTER PROCEDURE [dbo].[InsertComment]
  @commentDateline   datetime,
  @commentContent   varchar(200),
  @commentUserNickName  varchar(15),
  @commentIPAddress  varchar(50),
  @commentPrefixID   int
 AS
  INSERT INTO Comments
   ( 
    commentDateline,
    commentContent,
    commentUserNickName,
    commentUserIPAddress
   )
   VALUES
   (
    @commentDateline,
    @commentContent,
    @commentUserNickName,
    @commentIPAddress
   )
  
  DECLARE @lastcommentID INTEGER;
  SET @lastcommentID = @@IDENTITY;
   
  INSERT INTO comment_Prefixes
   ( 
    commentID,
    prefixID
   )
   VALUES
   (
    @lastcommentID,
    @commentPrefixID
   )

I’ve tried wrapping my entire procedure in BEGIN TRANSACTION and COMMIT TRANSACTION and that doesn’t solve the problem either.

OMG… Why can’t I edit my posts???

EDIT:

OK, I added this code at the bottom:


[SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]@@ERROR [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]<>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0 [/SIZE]
[SIZE=2]
            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ROLLBACK [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TRANSACTION[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]COMMIT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TRANSACTION[/COLOR][/SIZE][/COLOR][/SIZE]


And it seems to be working… is this the right way to do it?


ALTER PROCEDURE [dbo].[InsertComment]
  @commentDateline   datetime,
  @commentContent   varchar(200),
  @commentUserNickName  varchar(15),
  @commentIPAddress  varchar(50),
  @commentPrefixID   int
 AS
BEGIN TRANSACTION
  INSERT INTO Comments
   ( 
    commentDateline,
    commentContent,
    commentUserNickName,
    commentUserIPAddress
   )
   VALUES
   (
    @commentDateline,
    @commentContent,
    @commentUserNickName,
    @commentIPAddress
   )
  
  DECLARE @lastcommentID INTEGER;
  SET @lastcommentID = @@IDENTITY;
   
  INSERT INTO comment_Prefixes
   ( 
    commentID,
    prefixID
   )
   VALUES
   (
    @lastcommentID,
    @commentPrefixID
   )
[SIZE=2][COLOR=#0000ff]IF [/COLOR][COLOR=#ff00ff][COLOR=#ff00ff]@@ERROR [/COLOR][/COLOR][COLOR=#808080][COLOR=#808080]<>[/COLOR][/COLOR] 0 [/SIZE]
[SIZE=2]
            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ROLLBACK [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TRANSACTION[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]        
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]            
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]COMMIT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TRANSACTION[/COLOR][/SIZE][/COLOR][/SIZE]

Thanks.