July 21, 2013 at 11:16 pm
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]
FROMINSERTED
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:
[p]
(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.[/p]
I want to continue execution in procedure, but when rollback occur in trigger, the procedure p1 ended.
July 22, 2013 at 3:16 am
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
July 22, 2013 at 3:34 am
Thanks for reply.
I'm used SQL Server 2000.
July 22, 2013 at 8:01 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 9:23 pm
This is part of trigger and procedure and insert row by row. I don't insert batch of rows to the table. row
July 23, 2013 at 7:14 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply