Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Continue control from rollback in trigger to procedure Expand / Collapse
Author
Message
Posted Sunday, July 21, 2013 11:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
Points: 11, Visits: 107
Hi Dear,

CREATE TABLE TestTable
(
ID INT,
Name NVARCHAR(100)
)
GO

CREATE TRIGGER TestTable_Insert
ON TestTable
FOR INSERT
AS
DECLARE @ID INT

SELECT @ID = [ID]
FROM INSERTED

IF @ID = 1
ROLLBACK TRANSACTION

GO

CREATE PROCEDURE p1
AS
DECLARE @TestTable TABLE (ID INT, NAME NVARCHAR(100))

INSERT INTO @TestTable
([ID], [NAME])
VALUES (1, -- ID - int
N'Omid' -- NAME - nvarchar(100)
)

INSERT INTO @TestTable
([ID], [NAME])
VALUES (2, -- ID - int
N'Saeed' -- NAME - nvarchar(100)
)

DECLARE @ID INT, @Name NVARCHAR(100)

WHILE EXISTS (SELECT 1 FROM @TestTable)
BEGIN
BEGIN TRAN User_Tran

SELECT TOP 1
@ID = [ID],
@Name = Name
FROM @TestTable

INSERT INTO TestTable
( ID, Name)
VALUES ( @ID, @Name)

DELETE FROM @TestTable WHERE ID = @ID

COMMIT TRANSACTION User_Tran
END
GO

Execute:
EXECUTE p1        
GO

SELECT * FROM [Testtable]
GO

Result:


(1 row(s) affected)

(1 row(s) affected)
Msg 3609, Level 16, State 1, Procedure p1, Line 28
The transaction ended in the trigger. The batch has been aborted.



I want to continue execution in procedure, but when rollback occur in trigger, the procedure p1 ended.
Post #1475860
Posted Monday, July 22, 2013 3:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
You posted this question in the SQL 2000 forum, so I don't know if you can use this.
If you are on SQL version 2005+ then you have the ability to use a TRY...CATCH block. If you alter the procedure like the code below, the error message is catched and won't be displayed. The procedure continues without errors.
ALTER PROCEDURE p1
AS
DECLARE @TestTable TABLE (ID INT, NAME NVARCHAR(100))

INSERT INTO @TestTable
([ID], [NAME])
VALUES (1, -- ID - int
N'Omid' -- NAME - nvarchar(100)
)
INSERT INTO @TestTable
([ID], [NAME])
VALUES (2, -- ID - int
N'Saeed' -- NAME - nvarchar(100)
)

DECLARE @ID INT, @Name NVARCHAR(100)

WHILE EXISTS (SELECT 1 FROM @TestTable)
BEGIN
BEGIN TRAN User_Tran

SELECT TOP 1
@ID = [ID],
@Name = Name
FROM @TestTable
begin try -- try catch block added
INSERT INTO TestTable
( ID, Name)
VALUES ( @ID, @Name)
end try -- try catch block added
begin catch -- try catch block added
end catch -- try catch block added
DELETE FROM @TestTable WHERE ID = @ID
if @@trancount > 0 -- only commit transaction when there is an open transaction
COMMIT TRANSACTION User_Tran
END
GO



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1475904
Posted Monday, July 22, 2013 3:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
Points: 11, Visits: 107
Thanks for reply.
I'm used SQL Server 2000.
Post #1475909
Posted Monday, July 22, 2013 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
The biggest issue you have here is that your trigger is flawed. It will only handle single row inserts. Also, from the code you posted I don't see any reason at all why you need a loop for these inserts (other than the flawed trigger).

Since it looks like all you want that trigger to do is to NOT insert any rows when the ID is 1 you might consider using an instead of trigger.

CREATE TRIGGER TestTable_Insert
ON TestTable
INSTEAD OF INSERT
AS
insert TestTable (ID, Name)
select ID, Name
from inserted
where ID > 1 or ID < 1

This trigger will now handle any number of rows being inserted.

Now you just need to change up your procedure and drop the looping.

CREATE PROCEDURE p1
AS
DECLARE @TestTable TABLE (ID INT, NAME NVARCHAR(100))

INSERT INTO @TestTable
([ID], [NAME])
VALUES (1, -- ID - int
N'Omid' -- NAME - nvarchar(100)
)

INSERT INTO @TestTable
([ID], [NAME])
VALUES (2, -- ID - int
N'Saeed' -- NAME - nvarchar(100)
)

begin transaction

INSERT INTO TestTable( ID, Name)
select ID, Name
from @TestTable

COMMIT TRANSACTION
GO

--test it out
exec p1

go

--Did it work?
select * from TestTable



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476026
Posted Monday, July 22, 2013 9:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
Points: 11, Visits: 107
This is part of trigger and procedure and insert row by row. I don't insert batch of rows to the table. row
Post #1476335
Posted Tuesday, July 23, 2013 7:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
omid.shokri (7/22/2013)
This is part of trigger and procedure and insert row by row. I don't insert batch of rows to the table. row


Careful now. Do not get stuck in the mindset that because you have a procedure that does inserts row-by-row (which is an issue in its own right) that you will never do multiple row inserts. This is shortsighted and will come back to bite you one day. There was a story around here a couple of years about a company that literally went bankrupt because they had triggers like this that always assumed there was only 1 row. It is so easy to make your triggers handle multiple row inserts there really is no argument that would come close to convincing me otherwise.

Why does your procedure do the inserts one row at a time? Wouldn't is be a lot easier if it could do them all at once? Or is this an insert proc that receives values in which case that makes total sense.

Consider what will happen with your single row trigger when you someday have to upload 10,000 rows to this table. You either have to hope that whoever loads those rows remembers that the trigger has a flaw and the rows have to loaded one at a time or the insert will happen as a batch and your trigger will fire once and capture data for only 1 row.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse