December 10, 2010 at 4:57 pm
Hi,
Can anyone help pls.
I need to delete rows from multiple tables. I cannot use ON CASCADE DELETE because the tables don't really have foreign key constraints on them. I am hoping i can achieve this manually with a stored procedure and I am struggling with it. I want to delete all records that were loaded from a specific Filename/Feed from the main SOURCE table and consequently from the rest of the tables below where records from that File has been loaded into e.g.
1. Get / Delete all the records with FileNameA from the SOURCE table
2. Delete corresponding records from PERSON table (where SourceID, PersonID & AdressID match SOURCE table)
3. Delete corresponding records from ADDRESS table (where SourceID & AdressID match SOURCE table)
4. Delete corresponding records from BOOKING table (where SourceID, PersonID & AdressID match SOURCE table)
5. Delete corresponding records from BOOKING’s 3 children tables - FLIGHTS, ACCOMODATION & TOUR (where BookingID match BOOKING table)
Here's a little summary of what happens when we receive a file from the client and the tables involved in the loading process – and therefore the “un-loading" / delete process too.
1. First we load the major fields in the file into a SOURCE table which contains the SourceID (identity field), PersonID (from the file), AdressID (from the file), FileName, Loaddate etc so we can track the origin of the records .
2. Then we load the name part into the PERSON table, which has the PersonID, but also the SourceID & AdressID from the SOURCE table. The PersonID is distinct on this table as only new PersonIDs are added - duplicate "new" ones are archived based on a rule and the one with the "best" data is assigned as the current one.
3. Then we load the address part into the ADDRESS table, which has AdressID, but also the SourceID from the SOURCE table. Again, The AdressID is distinct on this table due to archiving.
4. Now we load the transactional booking data into the relevant tables - BOOKING and its 3 children (BOOKINGEXTRA, BOOKINGACCOMODATION & BOOKNINGTOUR)
5. BOOKING table has a BookingID as well as the SourceID, PersonID & AdressID from the main SOURCE table.
6. Then BOOKING’s 3 children - FLIGHTS, ACCOMODATION & TOUR are finally loaded and linked to BOOKING table via the BookingID.
That's all folks - sorry for the long explanation.
Sorry – one more thing, is it possible to write all these records into a journal table before they are deleted?
Thank you for your help on this.
December 10, 2010 at 5:12 pm
You've got the idea. I personally would delete from the bottom tables up, just so when you do get around to properly putting in foreign keys this doesn't break.
Your journal can be handled pretty easily with an audit trigger. Basically you create a copy of the table with a few extra audit columns, and an on delete trigger to copy the rows you're removing over to the audit table.
You might want to make sure all that data is put into a staging table FIRST, as a reference, before you start the deletes. You'll also want to encapsulate the majority, if not entirety, in a transaction.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 11, 2010 at 5:09 pm
how can i put the whole thing inside a transaction in the stored procedure?
Can anyone pls help with maybe a skeleton of what the stored procedure should look like?
December 12, 2010 at 10:55 am
Hi,
I'm not sure if this is what you're looking for but i would imagine this SP could look something like this:
create procedure name
(
parameters
)
as
begin
declare @errorno as int,
declare @errormsg as varchar(100)
declare @errorline as int
-- Do some parameter checking if necessary
-- everything OK? start transaction
begin tran
--wrap code in try/catch block to enable rollback in case of an error
begin try
-- Perform deletes here
delete tablenamex
where ...
delete tablename y
where ...
-- Commit if everything went OK
commit tran
end try
begin catch
-- something went wrong, display error information and rollback
select error_line(), error_number(), error_message()
rollback tran
end catch
end
Also see http://msdn.microsoft.com/en-us/library/ms179495.aspx for retrieving error information.
Cheers,
Willem
January 25, 2011 at 10:47 am
Hi,
I've only just realised that I never said thank you for your help with this query. So, thanks very much guys - much appreciated
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply