Rollback in Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rollback in Triggers

Author

Topic

Dhaliwal
Starting Member

5 Posts

Posted - 2008-02-18 : 05:31:13

Hello Everybody,

I am new to SQL Server... and I am having some problem in using rollback in triggers... Actually what I want to do is that if there are 2 or more rows that are being updated with and update query.. then the trigger should rollback the query, I mean the only update query that should work affect only a single record..... I am able to achieve this task....

Now, I want that if the rollback occurs(i.e. Multiple rows affects)... then the trigger should save the result of the query into a temperory table...

I have made this trigger.. that is able to rollback the transaction and not saving the records in a temprory table...

Create trigger trig_StopAction
on tbl_Users
for update as
begin
declare @num_updated int
select @num_updated = @@rowcount

if @num_updated > 1
Begin
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tbl_TEMP')
drop table tbl_TEMP
select * into tbl_TEMP from Inserted
select 'Failed'
rollback transaction
End
else
Begin
select str(@num_updated) + ' row Updated'
End
end

I think that the rollback transcation is rolling back the insert transaction also....
Is there any task to accomplish this task....
Thanks.....

Gurpreet Singh Dhaliwal

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 05:34:48

Change place for ROLLBACK statement?
Place it before select * into statement?

E 12°55'05.25"
N 56°04'39.16"

Dhaliwal
Starting Member

5 Posts

Posted - 2008-02-18 : 06:11:30

quote:
Originally posted by Peso

Change place for ROLLBACK statement?
Place it before select * into statement?

well... thanks for such a quick reply..... I have tried that also..... then it creates a table but do not save any records into the table...... it saves the structure of the table.... but no rows are saved...
Anything else that I can try??

Gurpreet Singh Dhaliwal

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 06:21:26

Table variables are not affected by rollbacks

Create trigger trig_StopAction on tbl_Users
for update
as
begin
	declare @num_updated int

	declare @temp table (same columns and definitions as tbl_users)

	select @num_updated = @@rowcount

	if @num_updated > 1
		Begin
			IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tbl_TEMP') 
				drop table tbl_TEMP

			insert @temp
			select * from Inserted 

			select 'Failed'

			rollback transaction

			select * into tbl_TEMP from @temp

		End
	else
		select '1 row Updated'
end

E 12°55'05.25"
N 56°04'39.16"

Dhaliwal
Starting Member

5 Posts

Posted - 2008-02-18 : 08:05:12

That worked great..... Thanks for your time and replies

Gurpreet Singh Dhaliwal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources