Stored Procedures and Triggers

[JasonYeung]

Currently I have a table containing a trigger that rollsback an attempted insert if there is already a row containing the same item. (ie. if there is already a row with the same captain, the transaction gets reversed.) The following is my trigger:

CREATE TRIGGER trigger_teamstable ON dbo.TeamsTable
FOR INSERT
AS

SAVE TRANSACTION TeamSave
DECLARE @rcount int

SELECT @rcount = @@rowcount
IF @rcount = 0
RETURN

IF (SELECT count(*)
FROM TeamsTable tt, inserted i
WHERE tt.Captain = i.Captain) <> @rcount
BEGIN
ROLLBACK TRANSACTION TeamSave
END

This trigger does what I want. I can attempt to add a duplicate captain by typing insert statements in the Enterprise Managerand it rolls back the transaction. Even when I run a few insert statements in a row it doesn't fail.

The problem is when I sue a stored procedure. What the stored proccedure does is it fetches rows of another table and inserts them into the table with the trigger. What I want is that if the trigger fires because of a duplicate captain, it rolls back the transaction and continue running until all the rows have been inserted (or attempted to be).

What happens is that it inserts the rows until the trigger fires and then stops the entire stored procedure. The following is my stored procedure:

CREATE PROCEDURE sp_InsertTeamsTable AS
IF (@@ERROR > 0)
RETURN

/* SET CURSOR_CLOSE_ON_COMMIT OFF */

DECLARE TempTeam CURSOR FOR
SELECT Team, Captain
FROM TeamsTable_dump
DECLARE @team varchar (50)
DECLARE @captain varchar (50)

OPEN TempTeam
FETCH NEXT FROM TempTeam INTO @team, @captain

WHILE (@@FETCH_STATUS = 0)

BEGIN
BEGIN TRANSACTION
INSERT INTO TeamsTable
(Team, Captain)
VALUES
(@team, @captain)

FETCH NEXT FROM TempTeam INTO @team, @captain
COMMIT TRANSACTION
END

CLOSE TempTeam
DEALLOCATE TempTeam

Jason

 

[jagilman]

Just a thought, in Access I would use the error object to test for this duplicate value error. Try to follow your insert statement with an error test somthing like this.

If (@@Error > 0) --See if an error was produced
If (@@Error = YourError#) --Expose your error
(Resume or T-SQL equalivant) --Continue
Else
--Manage unexpected errors here

I am new to SQL Server and this is the path I would look into.
John A. Gilman
gms@uslink.net