Master/Detail table insertion.

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

This my solution using trigger and SET XACT_ABORT ::

create table A (a int)

go

create table B (a int)

create trigger t_A on A

after insert

as

set xact_abort off

if @.@.error <>0

begin

print 'error in master'

rollback tran

else

begin

commit tran

insert into b

select a from inserted

-- values ('gigi')

end

if you run

insert into a values (1) the value 1 is inserted in A and B

for testing how is behavior on error of inserting in B table, unn comment "-- values ('gigi')" and comment " select a from inserted " and run the command " alter triger t_A ...." ()modifying trigger)

if you run insert into a values (2) the value 2 will be insert in A table and inserting will not be possible in B table because is an error .

So you have to modify the operation of inserting in B

" insert into b

select a from inserted

-- values ('gigi')

"

as your business logic request.

|||

Thanks for the reply but i dont want to use trigers because it can affect the performance.

Thanks for giving time.

|||

Did you calculate how is affected the performance ? Obviously the trigger have to be avoided but in some cases it is usually.

|||You'll probably want to encapsulate the inserts within a transaction with logic to perform a ROLLBACK if anything goes wrong or a COMMIT when all of the work is complete.

There is a vast amount of information in Books Online on transaction management.

Put simply your logic might look something like this.

BEGIN TRANSACTION

INSERT INTO A
INSERT INTO B
INSERT INTO B
INSERT INTO B

IF NO ERROR THEN

COMMIT ELSE
ROLLBACK

|||Thanks Sir,

Please can u explain that how can i send array because according to your solution i will have to use array for my detail information for example if my master table is ORDER and my detail table is ORDER_DETAIL then the list of products are to be send in array which i dont know how will i do.

If you can provide a simple code i will be thankfull to you.

Thanks.