Incomplete database update - 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)

Incomplete database update

Author

Topic

vijmeena
Starting Member

10 Posts

Posted - 2002-12-30 : 01:57:00

I am updating 3-4 tables in a row. Is it possible that the second table does not get updated whereas the 3 and 4 table are updated. Or it is possible that the update of the table is delayed for sometime.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-30 : 02:16:53

update tbl1
update tbl2
update tbl3
update tbl4

if you do this then it is possible that any of teh updtes can fail while others succeed (but not delayed).
To make sure all or none are updated introduce transaction control and error handling.

begin tran
update tbl1
if @@error <> 0
begin
rollback tran
raiserror('failed update',16,-1)
return
end
update tbl2
if @@error <> 0
begin
rollback tran
raiserror('failed update',16,-1)
return
end
update tbl3
if @@error <> 0
begin
rollback tran
raiserror('failed update',16,-1)
return
end
update tbl4
if @@error <> 0
begin
rollback tran
raiserror('failed update',16,-1)
return
end
commit tran

to check if any rows are affected by the update
declare @error int, @rowcount int

update tbl4
select @error = @@error, @rowcount = @@rowcount
if @error <> 0 or @rowcount = 0
begin
rollback tran
raiserror('failed update',16,-1)
return
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources