May 20, 2008 at 3:25 am
Hi,
I have a stored procedure which performs following operation in the order
1. Drop few constraints.
2. Delete some records from tables.
3. Recreate the constraints.
I have enabled transaction in the stored procedure.
In case any error has occurred, the transaction will be rolled back.
In this case will it rollback the dropped constraint?
May 20, 2008 at 4:00 am
It depends on the severity of the error I suppose, but for errors like key violations or check constraint violations, or data errors, constraints are restored during rollback.
create table testtable(a int, b int)
alter table testtable add constraint uq__b unique (b)
go
insert testtable(a, b)
values (1, 1)
insert testtable(a, b)
values (1, 2)
insert testtable(a, b)
values (1, 3)
go
create procedure testproc
as
begin try
begin tran
print 'delete constraint'
alter table testtable drop constraint uq__b
print 'raise error'
select 1/0
delete testtable where b = 2
print 'add constraint'
alter table testtable add constraint uq__b unique (b)
commit
end try
begin catch
declare @err varchar(255)
set @err = error_message()
print 'error message: ' + @err
rollback
end catch
go
select count(*) [constraint exists before proc?]
from sys.indexes where name='uq__b'
exec testproc
select count(*) [constraint exists after proc?]
from sys.indexes where name='uq__b'
go
drop procedure testproc
drop table testtable
go
Regards
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 4:27 am
Thanks Rodak. So I believe my sp will work fine. 🙂
May 20, 2008 at 4:43 am
Hi Anoop,
I will prefer to use Custom Error Checking rather than Try-Catch method.
Can you just tell me why you wants to apply the Transaction on a DDL Command also.
Cheers!
Sandy.
--
May 20, 2008 at 6:11 am
I have to create an sp for data cleansing process which will be run every night using scheduler.
The tables have few bidirectional relationship which will not allow to delete data. I cannot changes the db design as the impact will be very high.
So I need to drop the constraints temporarily.
May 20, 2008 at 7:40 am
Just a question - if you have circular dependencies and you cannot delete data, how can you insert data to these tables? Do you drop constraints as well?
Piotr
...and your only reply is slàinte mhath
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply