Begin transaction error - 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)

Begin transaction error

Author

Topic

na2173
Starting Member

38 Posts

Posted - 2002-04-25 : 19:27:11

Here is an example

I created a table A, and inserting some values

Create table a (roll int)
insert into a values(1)

Create two tables called b and c
C is a child of b

create table b(stuid int Not null, stuname varchar(20))
create table c(stuid int, student_courses varchar(20))

insert into b values(300, 'gary')
insert into b values(301, 'Mary')

insert into c values(300, 'MATHS')
insert into c values(300, 'Cs')
insert into c values(301, 'MATHS')

alter table b add constraint pk_b_stuid primary key (stuid)

ALTER TABLE [c] ADD
CONSTRAINT [FK_c_b] FOREIGN KEY
(
[stuid]
) REFERENCES b (stuid)
GO

When i run the 1st query in a transaction , where hhh is not a object in database ,
the transaction should rollback , this query rolls back the transaction, so the contents in A are rolled back

But if i run the second query down in a transaction Then the contents of A is not rolled back
it commits the transaction and data is delted from Table A,
Where as the data is still in B, Since data in B cannot be delted because there are some rows in C which is
Refering it

My question is When there is an any error in the transaction, what ever DML Statement we run with in a transation it should roll back but
why is it commiting in the second query, is it a bug or i am doing some thing wrong , i know i can use @@error and trap the
error and then i can rollback transaction, but if i am not using any rollback transcation it should even rollback the
second query

begin tran
delete from a
delete from hhhh
commit tran

begin tran
delete from a
delete from b
commit tran

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-26 : 01:50:41

i just check it
in both the case from tabel a the rows are deleted

can we set the auto commit true or false like we do in oracel...?

======================================
Ask to your self before u ask someone

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-26 : 02:07:29

do you mean 'Implicit Transactions' ?

-Chad

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-26 : 02:21:26

thanks chadmat
u mean 'Implicit Transactions' sql will start trans and untill we give a commit or rollback the trans is not done..

by default it should be off

did u try out the na2173 problem what can be the problem...

======================================
Ask to your self before u ask someone

na2173
Starting Member

38 Posts

Posted - 2002-04-26 : 10:05:32

okay , for the first query if you look at sp_lock there will exclusive locks on the table, when you select data from table A within the same session you will not see in any records, please close the session and then try selecting the data from Table A.
but for the second query if you look sp_lock there will be no locks,

na2173
Starting Member

38 Posts

Posted - 2002-04-29 : 16:03:47

i finally found the answer my self
use SET XACT_ABORT ON before you begin the transaction(Automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources