Transaction not working -- help appreciated 
Author Message
 Transaction not working -- help appreciated

I have this:

USE pubs_copy
GO

BEGIN TRAN

UPDATE employee SET lname = "Jones" WHERE fname = "Paolo"

UPDATE publishers SET pub_id = 8888 WHERE city = "London"


 BEGIN
  PRINT "committing transaction"
  COMMIT TRAN
 END
ELSE
 BEGIN
  PRINT "Rolling Back"
  ROLLBACK TRAN
 END
GO

I know that the second of the updates fails as there is no city of London in
my publishers table.  But each time I run the damn thing I find that the
first update succeeds.

????

TIA

Nicholas



Mon, 03 Mar 2003 03:00:00 GMT
 Transaction not working -- help appreciated

Nicolas,

Change the IF statement to:


--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.
FAQ at: http://www.sql-server.co.uk/frmMain.asp


Quote:
> I have this:

> USE pubs_copy
> GO

> BEGIN TRAN

> UPDATE employee SET lname = "Jones" WHERE fname = "Paolo"

> UPDATE publishers SET pub_id = 8888 WHERE city = "London"


>  BEGIN
>   PRINT "committing transaction"
>   COMMIT TRAN
>  END
> ELSE
>  BEGIN
>   PRINT "Rolling Back"
>   ROLLBACK TRAN
>  END
> GO

> I know that the second of the updates fails as there is no city of London in
> my publishers table.  But each time I run the damn thing I find that the
> first update succeeds.

> ????

> TIA

> Nicholas



Mon, 03 Mar 2003 03:00:00 GMT
 Transaction not working -- help appreciated
I'll have a play with the If statement but as far as I can see it looks
good.  If both updates have been processed then commit else rollback.
What's wrong with that?

Thnx

Nicholas



Quote:
> Nicolas,

> Change the IF statement to:


> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.
> FAQ at: http://www.sql-server.co.uk/frmMain.asp



> > I have this:

> > USE pubs_copy
> > GO

> > BEGIN TRAN

> > UPDATE employee SET lname = "Jones" WHERE fname = "Paolo"

> > UPDATE publishers SET pub_id = 8888 WHERE city = "London"


> >  BEGIN
> >   PRINT "committing transaction"
> >   COMMIT TRAN
> >  END
> > ELSE
> >  BEGIN
> >   PRINT "Rolling Back"
> >   ROLLBACK TRAN
> >  END
> > GO

> > I know that the second of the updates fails as there is no city of
London in
> > my publishers table.  But each time I run the damn thing I find that the
> > first update succeeds.

> > ????

> > TIA

> > Nicholas



Mon, 03 Mar 2003 03:00:00 GMT
 Transaction not working -- help appreciated

Ooops, my fault. I didn't notice that you check IF = 0 (I generally check IF <> 0).

I believe that I've found it, though. You say that there is no supplier in London. But
updating zero rows is not an error (relationally speaking). That is a perfectly valid
update (an update modifies n rows, where n can be 0).


--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.
FAQ at: http://www.sql-server.co.uk/frmMain.asp


Quote:
> I'll have a play with the If statement but as far as I can see it looks
> good.  If both updates have been processed then commit else rollback.
> What's wrong with that?

> Thnx

> Nicholas



Mon, 03 Mar 2003 03:00:00 GMT
 Transaction not working -- help appreciated

Nicholas,

Quote:
>UPDATE publishers SET pub_id = 8888 WHERE city = "London"
>I know that the second of the updates fails as there is no city of London in
>my publishers table.

It does NOT fail.  Updating zero rows is not a failure in SQL.  The
UPDATE affected exactly the number of rows in the table that matched
the WHERE clause test.  It happens that this was zero rows, a
perfectly normal event.

An error is an instruction that can not be carried out.  If there were
a unique constraint that would be violated by an update, for example.
Or a referential integrity constraint.

This is all part of the change in thinking we all must make when
moving from row-by-row processing to set based relational processing.

Roy



Mon, 03 Mar 2003 03:00:00 GMT
 Transaction not working -- help appreciated
Thanks to all

Nicholas


Quote:

> Nicholas,

> >UPDATE publishers SET pub_id = 8888 WHERE city = "London"

> >I know that the second of the updates fails as there is no city of London
in
> >my publishers table.

> It does NOT fail.  Updating zero rows is not a failure in SQL.  The
> UPDATE affected exactly the number of rows in the table that matched
> the WHERE clause test.  It happens that this was zero rows, a
> perfectly normal event.

> An error is an instruction that can not be carried out.  If there were
> a unique constraint that would be violated by an update, for example.
> Or a referential integrity constraint.

> This is all part of the change in thinking we all must make when
> moving from row-by-row processing to set based relational processing.

> Roy



Mon, 03 Mar 2003 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. SQL 6.5 Transaction Log Problem - Help would be appreciated

2. BEGIN TRANSACTION is working for MSACCESS but not working for SQL Server

3. Ado Help Please Help any help is appreciated

4. HELP: Site not working (or working erratically) after Hosting company upgrades server

5. HELP - VB3/ACCESS 2 conversion to VB4/ACCESS 7 not working - Not MIKE LUSK

6. Dump Transaction does not work

7. Dump transaction does not work

8. SQL Server 2000 - Transaction Log not working?

9. Transaction Log Backup not working

10. Transaction Log Backup not working

11. Transaction Log backup not working

12. dump transaction does not work


 
Powered by phpBB® Forum Software