Hi experts
I have one main table and six related tables. Any single entry in the main table corresponds to multiple entry in each related table. I want to write a stored proc to delete an item from the main table and in turn should also delete multiple records in related tables that is associated with the item to be deleted. If any error happens in between, the whole transaction has to be rolled back. I cannot use MTS, so...
Please help me with some code samples ASAP. Any help is appreciated.


Sankar Sengupta

__________________
Sankar Sengupta
Striving for the BEST

Hi,

I would suggest you to write a trigger on the main table for delete operation, which internally deletes the corresponding records from the other related tables.

Please refer BOL for triggers.

All the best

Cheers
-Vijay G

If the related tables are defined as foreign keys, then you can set the ON DELETE CASCADE option on the foreign key constraints which establish the relationships.

With that enabled, simply DELETE from the "main" table, and the related rows in tables related to it will be automatically deleted. Wrap the DELETE in a transaction, test @@ERROR after the DELETE and if it non-zero, roll the transaction back, otherwise COMMIT it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Vijay,
Thanks for the quick reply.
I have some of the tables related with FK-PK relationship, but some are not. So is it okay to write a trigger? Besides, can there be control on the complete transaction? Also I would like to know the full form of BOL.
THX in adv.

Sankar Sengupta

Hi Sankar,

BOL = Books Online. You can find this in your Start Menu >>Programs>>MSSQL Server>>Books Online

Yes, any transaction in SQL is controlable.

You can embed it within "BEGIN TRAN" and "END TRAN"

And in any case of ERROR, you can "ROLLBACK TRAN" by checking for Value of @@ERROR server variable. On unsuccessfull conditions @@ERROR will have NON ZERO value.

Sample piece of code as given below. (May be someone has good ways to do it)

create trigger TRIGGER_NAME
on MAIN_TABLE
for DELETE
as
begin
        STATEMENT FOR TRANSACTION_1
    if @@Error<>0
        Begin
            RollBack Tran
            Return
        End
        STATEMENT FOR TRANSACTION_2
    if @@Error<>0
        Begin
            RollBack Tran
            Return
        End
         ...... So on.....
    commit tran
        Return
end

Cheers

-Vijay G

Thx that helped. Only thing is how can I have a parameter in a trigger?

Sankar Sengupta

Sankar,

Triggers do not take parameters. Stored procedures would be the ideal one for that.

Just wondering why you wanted to use parameters with it.

Cheers

-Vijay G