Deleting all without contraints

I have a table that has about 10 different foreign key and trigger
contraints. I am wanting to setup a delete that will delete records
that do not have a contraint problem.
The problem is that once it hits one record with a contraint problem it
stops I want it to continue on to the next record.
I first tried this:
DELETE FROM tblContact
WHERE sActiveFlag = 'F'
I then tried a cursor thinking I could check the error and continue but
it still stops:
DECLARE @.lContactId int
DECLARE cExchange SCROLL CURSOR FOR
select lContactId FROM tblContact
WHERE sActiveFlag = 'F'
order by lContactId
FOR READ ONLY
OPEN cExchange
FETCH FIRST FROM cExchange INTO
@.lContactId
WHILE @.@.FETCH_STATUS = 0
BEGIN
DELETE FROM tblContact
WHERE lContactId = @.lContactId
IF ( @.@.ERROR != 0 )
BEGIN
FETCH NEXT FROM cExchange INTO
@.lContactId
END
ELSE
BEGIN
FETCH NEXT FROM cExchange INTO
@.lContactId
END
select @.@.FETCH_STATUS, @.@.error
END
CLOSE cExchange
DEALLOCATE cExchange
Any help would be greatly appreciated.
Thanks,
DeidreDS wrote:
> I have a table that has about 10 different foreign key and trigger
> contraints. I am wanting to setup a delete that will delete records
> that do not have a contraint problem.
I suppose it's out of the question to just go through each constraint,
figure out what it means, and translate it into a WHERE subclause?
DBCC CHECKCONSTRAINTS('ttOrdClubItem') WITH ALL_CONSTRAINTS,
ALL_ERRORMSGS
may help.|||Thanks for the suggestion. I was trying not to do that because there
are so many contraints and some are around databases. Also this tables
has the potential to have alot more contraints added so I didn't want
to miss anything. Any other suggestions?|||Did you try disabling triggers and constraints?
alter table t1
nocheck constraint all
go
alter table t1
disable trigger all
go
delete ...
alter table t1
check constraint all
go
alter table t1
enable trigger all
go
AMB
"DS" wrote:

> Thanks for the suggestion. I was trying not to do that because there
> are so many contraints and some are around databases. Also this tables
> has the potential to have alot more contraints added so I didn't want
> to miss anything. Any other suggestions?
>|||If I disable the triggers and constraints wouldn't it then allow me to
delete the ones with contraints? I don't want to delete the ones with
contraints I want to skip those.
Deidre|||DS wrote:
> Thanks for the suggestion. I was trying not to do that because there
> are so many contraints and some are around databases. Also this tables
> has the potential to have alot more contraints added so I didn't want
> to miss anything. Any other suggestions?
After thinking it over some more, I realize that the DBCC command I
gave above is totally not what you want. You want to know which records
can be deleted without violating constraints; the DBCC command would
return which records are violating current constraints. Not the same
thing at all.
The only server-side solution I can think of is to use sp_execsql in a
cursor or some other loop:
DECLARE @.IContactId int
DECLARE @.returnStatus int
DECLARE cExchange SCROLL CURSOR FOR
select lContactId FROM tblContact
WHERE sActiveFlag = 'F'
order by lContactId
FOR READ ONLY
OPEN cExchange
FETCH FIRST FROM cExchange INTO @.lContactId
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXECUTE @.returnStatus = sp_executesql
N'DELETE FROM tblContact WHERE lContactId = @.theID',
N'@.theID int',
@.IContactId
-- if @.returnStatus is 0, delete succeeded; if not, it's 1.
-- you could do something with that fact now, if needed
FETCH NEXT FROM cExchange INTO @.lContactId
END
Short of that, you'd have to do your looping from an external program.|||Good suggestion! I really thought that was going to work but it still
stops once it hits the first constaint problem. Ugh!!!