Transaction over many tables

[maccaroo]

Hi. I need to insert a number of new records using a transaction. The script will be something like:

Create new record in Table1.
Create new records in Table2, with Table2FK linked to Table1PK
Create new records in Table3, with Table3FK linked to Table2PK
...

Is is possible to create a transaction which spans over a number of linked tables like that? I've been told that Table1PK is not really created until the transaction is commited, but that means I can't link the FK in the child tables until after the parent table's PK commits.

 

[SQLSister]

If you are using an identity field for the PK, you won;t have a problem. It inserts the records and creates the ID field, but it will roll them back if the transaction fails.

So the basic process is to Begin the transaction, perofrm the first insert Check for errors. Select the PK field Use scope_identity() to grab it if using an idnetity field as the PK. If no errors, Perform the next insert, check for errors. If none perform the next and so on. At the end commit if there are no errors and rollback if there are errors. You check errors by grabbing the value of the @@error variable. Remeber this value is reset when you perform the next action, so you will want to set this value to a local variable if you want to preserve it later in the SP.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief