Nested TransactionScopes in CLR Stored Proc

I have a CLR stored proc and have a outer transactionscope and multiple inner Transactionscopes but when I run the code and the inner transactionscope does not call Complete it is still commmiting the insertion of data.

The inner TransactionScopes use RequiresNew.

Any ideas why, code below?

[code]

[Microsoft.SqlServer.Server.SqlProcedure]
public static void spCreateAllocations(int campaignid, string product, DateTime startdate, double rate, int numweeks, int bonuses, string umguser)
{
    using (TransactionScope trans = new TransactionScope())
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        string sql = "";
        DateTime actualStart;

        actualStart = GetActualStartDate(startdate);

        connection.Open();

开发者_如何学编程        int hdrid = CreateAllocationHeader(connection, campaignid, rate, numweeks, bonuses, umguser);

        sql = "SELECT * FROM VenuePanels WHERE SelectRow<>0";

        SqlDataAdapter adp = new SqlDataAdapter(sql, connection);
        DataTable dt = new DataTable();

        adp.Fill(dt);

        if (dt.Rows.Count == 0)
            return;

        bool allocated = false;

        foreach (DataRow dr in dt.Rows)
        {
            for (int i = 1; i <= numweeks + bonuses; i++)
            {
                if (i <= numweeks)
                    allocated = ProcessPanel(connection, hdrid, campaignid, dr, actualStart.AddDays(7 * (i - 1)), rate, numweeks, false, umguser);
                else
                    allocated = ProcessPanel(connection, hdrid, campaignid, dr, actualStart.AddDays(7 * (i - 1)), rate, numweeks, true, umguser);
                if(!allocated)
                    throw new Exception("ERROR: These panels have already been allocated. Please Check the panel allocations!");
            }
        }

        ProcessBursts(connection, hdrid, dt, campaignid, product, umguser);

        //throw new Exception("Buggin out Here");

        LogAllocationErrors(connection);

        trans.Complete();
    }
}

private static void ProcessBursts(SqlConnection connection, int hdrid, DataTable dtPanels, int campaignid, string product, string umguser)
{
    LoadBursts(connection, campaignid, product);
    bool burstallocated = false;
    bool bonusallocated = false;
    int lastweek = 0;

    foreach (DataRow dr in dtPanels.Rows)
    {
        foreach (Burst burst in _bursts)
        {
            using (TransactionScope trans = new TransactionScope(TransactionScopeOption.RequiresNew))
            {
                for (int i = 1; i <= burst.NumWeeks + burst.Bonuses; i++)
                {
                    if (i <= burst.NumWeeks)
                        burstallocated = ProcessPanel(connection, hdrid, burst.CampaignID, dr, burst.StartDate.AddDays(7 * (i - 1)), burst.Rate, burst.NumWeeks, false, umguser);
                    else
                        bonusallocated = ProcessPanel(connection, hdrid, burst.CampaignID, dr, burst.StartDate.AddDays(7 * (i - 1)), burst.Rate, burst.NumWeeks, true, umguser);
                    if (!burstallocated)
                    {
                        lastweek = i;
                        break;
                    }
                }

                if (burstallocated)
                    trans.Complete();
                else
                    LogAllocationError(Convert.ToInt32(dr["PanelID"]), burst.CampaignID, lastweek, burst.NumWeeks, burst.Bonuses);
            }
        }
    }
}

[/code]

---

No such thing as nested transactions?