JasonYeung
Programmer
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
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